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

1.Conditional Formatting

Sub MultipleConditionalFormatting()

Dim MyRange As Range

'Create range object

Set MyRange = Range("A1:DR307511")

'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(0, 255, 0)

'Add second rule

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

Formula1:="=100"

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

'Add third rule

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

Formula1:="=150"

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

End Sub

2. Creating PDF

Public Sub pdf()

ThisWorkbook.Sheets(Array("Dashboard")).Select 'insert sheet name to print in pdf

ActiveSheet.ExportAsFixedFormat _

Type:=xlTypePDF, _

Filename:="Sample Excel File Saved As PDF", _

Quality:=xlQualityStandard, _

IncludeDocProperties:=True, _

IgnorePrintAreas:=False, _

OpenAfterPublish:=True

End Sub

3.Spell Check

Public Sub spellcheck()

Dim cell As Range

For Each cell In ActiveSheet.UsedRange

If Not Application.CheckSpelling(Word:=cell.Text) Then cell.Interior.ColorIndex = 3

Next

End Sub

Drive link - https://drive.google.com/drive/folders/1laYDoYpK__4RPgVQhP3uBh3YmPN0xiqF?dmr=1&ec=wgc-drive-globalnav-goto&hl=en_GB