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

File Link : https://docs.google.com/spreadsheets/d/1biaZW-tLMy3qtwyoLQhl-KilXDy9j6MK/edit?usp=share_link&ouid=112587880867516540922&rtpof=true&sd=true

Q3) b: VBA Code to convert selected data into pdf from applicant data

Sub SaveToPDF()
Dim rg As Range
Dim flname As String
Dim fllocation As String
Set rg = Application.Selection
flname = Application.InputBox("Please Enter a name for the new PDF")
pdfpath = ThisWorkbook.Path & "\" & flname
rg.ExportAsFixedFormat Type:=xlTypePDF, _
filename:=flname
End Sub

Q3) c : select the first cell in the column

Public Sub Spellingchecking()
Dim i As Integer
Dim r As Integer
Dim c As Integer
Dim f As Integer

r = ActiveCell.Row
c = ActiveCell.Column
f = 1

Do While f = 1
If r > ActiveSheet.UsedRange.Rows.Count Then
f = 0
Else
Cells(r, c).CheckSpelling
r = r + 1
f = 0
End If
Loop

End Sub

Q3) a: Macro for Data Formatting

Sub Project2_format()
'
' Project2_format Macro
'

'
Columns("H:H").Select
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
Columns("I:I").Select
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("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 = 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 = 16776444
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
.Color = 13011546
.TintAndShade = 0
End With
Columns("K:K").Select
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueLowestValue
With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 10285055
.TintAndShade = 0
End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueHighestValue
With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8109667
.TintAndShade = 0
End With
Rows("1:1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Range("G3").Select
End Sub