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

Link for Excel

https://docs.google.com/spreadsheets/d/1fvCL9OOgnRNOzMQwhcKUozYySeunTV60/edit?usp=share_link&ouid=113410690249939411769&rtpof=true&sd=true

Macro:

Sub freeze_panes()
'
' freeze_panes Macro
'

'
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Columns("J:J").Select
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 8109667
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 7039480
.TintAndShade = 0
End With
Columns("DQ:DQ").Select
Selection.FormatConditions.AddIconSetCondition
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.ReverseOrder = False
.ShowIconOnly = False
.IconSet = ActiveWorkbook.IconSets(xl3TrafficLights1)
End With
With Selection.FormatConditions(1).IconCriteria(2)
.Type = xlConditionValuePercent
.Value = 33
.Operator = 7
End With
With Selection.FormatConditions(1).IconCriteria(3)
.Type = xlConditionValuePercent
.Value = 67
.Operator = 7
End With
Range("DR14").Select
End Sub

VBA Code to convert selected data into pdf:

Public Sub pdf_range()

Dim ws As Worksheet
Dim lastRow As Long

ChDir "C:\Users\aakas\Downloads\project 2"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\aakas\Downloads\project 2", OpenAfterPublish:=True

Set ws = Sheets("About Applicant Data")
Set rng = ws.Range("A1:D25")

MsgBox "The last row which has data in Col A of Sheet1 is " & lastRow

End Sub

Check Spelling mistakes in the workbook using VBA Code:

Public Sub check_spell()

ActiveSheet.CheckSpelling

End Sub