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

VBA code for conditional formatting :

Sub conditional_formatting()
'
' conditional_formatting Macro
'

'
    Range("A1:DR307512").Select
    ActiveWindow.ScrollRow = 303744
    ActiveWindow.ScrollRow = 218717
    ActiveWindow.ScrollRow = 170054
    ActiveWindow.ScrollRow = 157755
    ActiveWindow.ScrollRow = 148664
    ActiveWindow.ScrollRow = 135295
    ActiveWindow.ScrollRow = 122995
    ActiveWindow.ScrollRow = 120856
    ActiveWindow.ScrollRow = 117113
    ActiveWindow.ScrollRow = 113370
    ActiveWindow.ScrollRow = 110161
    ActiveWindow.ScrollRow = 107487
    ActiveWindow.ScrollRow = 104813
    ActiveWindow.ScrollRow = 103744
    ActiveWindow.ScrollRow = 102140
    ActiveWindow.ScrollRow = 100001
    ActiveWindow.ScrollRow = 98931
    ActiveWindow.ScrollRow = 96257
    ActiveWindow.ScrollRow = 87166
    ActiveWindow.ScrollRow = 86097
    ActiveWindow.ScrollRow = 85562
    ActiveWindow.ScrollRow = 84493
    ActiveWindow.ScrollRow = 82354
    ActiveWindow.ScrollRow = 81284
    ActiveWindow.ScrollRow = 76471
    ActiveWindow.ScrollRow = 73797
    ActiveWindow.ScrollRow = 70054
    ActiveWindow.ScrollRow = 67915
    ActiveWindow.ScrollRow = 63637
    ActiveWindow.ScrollRow = 60428
    ActiveWindow.ScrollRow = 57755
    ActiveWindow.ScrollRow = 54546
    ActiveWindow.ScrollRow = 53477
    ActiveWindow.ScrollRow = 46525
    ActiveWindow.ScrollRow = 43851
    ActiveWindow.ScrollRow = 42247
    ActiveWindow.ScrollRow = 39038
    ActiveWindow.ScrollRow = 35295
    ActiveWindow.ScrollRow = 31017
    ActiveWindow.ScrollRow = 29947
    ActiveWindow.ScrollRow = 26204
    ActiveWindow.ScrollRow = 22460
    ActiveWindow.ScrollRow = 20856
    ActiveWindow.ScrollRow = 19787
    ActiveWindow.ScrollRow = 18182
    ActiveWindow.ScrollRow = 14974
    ActiveWindow.ScrollRow = 11765
    ActiveWindow.ScrollRow = 11230
    ActiveWindow.ScrollRow = 10161
    ActiveWindow.ScrollRow = 8022
    ActiveWindow.ScrollRow = 6952
    ActiveWindow.ScrollRow = 6418
    ActiveWindow.ScrollRow = 5348
    ActiveWindow.ScrollRow = 3744
    ActiveWindow.ScrollRow = 1070
    ActiveWindow.ScrollRow = 535
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 100
    ActiveWindow.ScrollColumn = 98
    ActiveWindow.ScrollColumn = 93
    ActiveWindow.ScrollColumn = 85
    ActiveWindow.ScrollColumn = 77
    ActiveWindow.ScrollColumn = 71
    ActiveWindow.ScrollColumn = 65
    ActiveWindow.ScrollColumn = 59
    ActiveWindow.ScrollColumn = 54
    ActiveWindow.ScrollColumn = 49
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 41
    ActiveWindow.ScrollColumn = 38
    ActiveWindow.ScrollColumn = 35
    ActiveWindow.ScrollColumn = 32
    ActiveWindow.ScrollColumn = 29
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    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 = 7039480
        .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 = 8109667
        .TintAndShade = 0
    End With
    Range("L6").Select
    Columns("L:L").EntireColumn.AutoFit
    Columns("K:K").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
End Sub

VBA code to freezing panes:

Sub Freezing_Panes()
'
' Freezing_Panes Macro
'

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

VBA code to convert data into pdf :

Sub PDFconversion()
Dim r As Range
Dim s As String

If Selection.Count = 1 Then
Set r = Application.InputBox("Select a range", "Get Range", Type:=8)
Else
Set r = Selection
End If

strfile = "Selection" & "_" _
& Format(Now(), "yyyymmdd_hhmmss") _
& ".pdf"
strfile = ThisWorkbook.Path & "\" & strfile

myfile = Application.GetSaveAsFilename _
(InitialFileName:=strfile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save as PDF")
If myfile <> "False" Then
r.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
End If
End Sub