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/1KRnfihGUEQrxurNEopKORl6HWu743nqA/edit?usp=drive_link&ouid=103240038250889231916&rtpof=true&sd=true - link for the project

• Create a macro for conditional formatting & freezing panes

Sub ApplyConditionalFormattingAndFreezePanes()
' Conditional Formatting
Cells.Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = RGB(255, 255, 0)
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = RGB(0, 255, 0)
With Selection.FormatConditions(1).ColorScaleCriteria(1)
.Value = 0
End With
With Selection.FormatConditions(1).ColorScaleCriteria(2)
.Value = 50
End With
Selection.FormatConditions(1).ScopeType = xlWholeWorksheet

' Freezing Panes
With ActiveWindow
.SplitColumn = 1
.SplitRow = 1
.FreezePanes = True
End With
End Sub

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

Sub ConvertSelectedDataToPDF()
Dim rng As Range
Dim ws As Worksheet
Dim savePath As String

' Set the range to the selected data
Set rng = Selection

' Set the worksheet to the active sheet or specify a specific sheet
Set ws = ActiveSheet

' Specify the save path and filename for the PDF
savePath = "C:\Path\to\save\file.pdf" ' Update with the desired save location and filename

' Export the selected range as PDF
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

• Check Spelling mistakes in the workbook using VBA Code

Sub CheckSpellingInWorkbook()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Activate
Cells.CheckSpelling
Next ws
End Sub