Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

https://docs.google.com/spreadsheets/d/1VEDQWxGO4YmRHl2AL5jRPsBhx_m0dbvb/edit?usp=sharing&ouid=114373299790619730428&rtpof=true&sd=true

Create a macro for conditional formatting & freezing panes

Public Sub FormattingExample()

Dim MyRange As Range

'Create range object

Set MyRange = Range(“A1:A10”)

'Delete previous conditional formats

MyRange.FormatConditions.Delete

'Add first rule

MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _

Formula1:="=100", Formula2:="=150"

MyRange.FormatConditions(1).Interior.Color = RGB(255, 0, 0)

'Add second rule

MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _

Formula1:="=100"

MyRange.FormatConditions(2).Interior.Color = vbBlue

'Add third rule

MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _

Formula1:="=150"

MyRange.FormatConditions(3).Interior.Color = vbYellow

End Sub

Create VBA Code to convert selected data into pdf from applicant data

Public Sub SaveAsPDF()

Dim WS As Worksheet

Dim FileName As String

Dim FilePath As String

Set WS = ActiveSheet

FileName = "Result PDF"

WS.PageSetup.Orientation = xlPortrait

FilePath = ThisWorkbook.Path

WS.ExportAsFixedFormat Type:=xlTypePDF, FileName:=FilePath & "\" & FileName & ".pdf", _ Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False

MsgBox "Completed", vbInformation, ""

End Sub

Check Spelling mistakes in the workbook using VBA Code

Public Sub Spell_Check()

Dim Rng As Range

For Each Rng In ActiveSheet.UsedRange

If Not Application.CheckSpelling(Rng.Text) Then

Rng.Interior.Color = RGB(255, 0, 0)

End If

Next Rng

End Sub