Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
1 and 2 ) It is in Excel.For your reference
3a)
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+f
'
Rows("1:1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll Down:=-27
Columns("H:H").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=58512825"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=57
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 734
ActiveWindow.ScrollRow = 2198
ActiveWindow.ScrollRow = 5859
ActiveWindow.ScrollRow = 8788
ActiveWindow.ScrollRow = 12448
ActiveWindow.ScrollRow = 19038
ActiveWindow.ScrollRow = 26359
ActiveWindow.ScrollRow = 36609
ActiveWindow.ScrollRow = 41734
ActiveWindow.ScrollRow = 52716
ActiveWindow.ScrollRow = 60038
ActiveWindow.ScrollRow = 65163
ActiveWindow.ScrollRow = 76145
ActiveWindow.ScrollRow = 81270
ActiveWindow.ScrollRow = 90055
ActiveWindow.ScrollRow = 101037
ActiveWindow.ScrollRow = 109823
ActiveWindow.ScrollRow = 122269
ActiveWindow.ScrollRow = 125930
ActiveWindow.ScrollRow = 136180
ActiveWindow.ScrollRow = 141305
ActiveWindow.ScrollRow = 150091
ActiveWindow.ScrollRow = 155216
ActiveWindow.ScrollRow = 158144
ActiveWindow.ScrollRow = 164001
ActiveWindow.ScrollRow = 165466
ActiveWindow.ScrollRow = 172055
ActiveWindow.ScrollRow = 174984
ActiveWindow.ScrollRow = 181573
ActiveWindow.ScrollRow = 185966
ActiveWindow.ScrollRow = 189626
ActiveWindow.ScrollRow = 193287
ActiveWindow.ScrollRow = 196216
ActiveWindow.ScrollRow = 199144
ActiveWindow.ScrollRow = 199876
ActiveWindow.ScrollRow = 201341
ActiveWindow.ScrollRow = 202073
ActiveWindow.ScrollRow = 202805
ActiveWindow.ScrollRow = 203537
ActiveWindow.ScrollRow = 204269
ActiveWindow.ScrollRow = 205001
ActiveWindow.ScrollRow = 207198
ActiveWindow.ScrollRow = 209394
ActiveWindow.ScrollRow = 210126
ActiveWindow.ScrollRow = 212323
ActiveWindow.ScrollRow = 213055
ActiveWindow.ScrollRow = 215983
ActiveWindow.ScrollRow = 217448
ActiveWindow.ScrollRow = 218180
ActiveWindow.ScrollRow = 218912
ActiveWindow.ScrollRow = 221108
ActiveWindow.ScrollRow = 222573
ActiveWindow.ScrollRow = 224769
ActiveWindow.ScrollRow = 225501
ActiveWindow.ScrollRow = 227698
ActiveWindow.ScrollRow = 228430
ActiveWindow.ScrollRow = 231358
ActiveWindow.ScrollRow = 233555
ActiveWindow.ScrollRow = 235019
ActiveWindow.ScrollRow = 237216
ActiveWindow.ScrollRow = 240144
ActiveWindow.ScrollRow = 243073
ActiveWindow.ScrollRow = 243805
ActiveWindow.ScrollRow = 245269
ActiveWindow.ScrollRow = 246001
ActiveWindow.ScrollRow = 246733
ActiveWindow.ScrollRow = 247465
ActiveWindow.ScrollRow = 248198
ActiveWindow.ScrollRow = 248930
ActiveWindow.ScrollRow = 250394
ActiveWindow.ScrollRow = 251858
ActiveWindow.ScrollRow = 256983
ActiveWindow.ScrollRow = 257715
ActiveWindow.ScrollRow = 260644
ActiveWindow.ScrollRow = 261376
ActiveWindow.ScrollRow = 266501
ActiveWindow.ScrollRow = 270894
ActiveWindow.ScrollRow = 273090
ActiveWindow.ScrollRow = 274555
ActiveWindow.ScrollRow = 275287
ActiveWindow.ScrollRow = 276751
ActiveWindow.ScrollRow = 278948
ActiveWindow.ScrollRow = 281876
ActiveWindow.ScrollRow = 284073
ActiveWindow.ScrollRow = 287001
ActiveWindow.ScrollRow = 292126
ActiveWindow.ScrollRow = 292858
ActiveWindow.ScrollRow = 297251
ActiveWindow.ScrollRow = 301644
ActiveWindow.ScrollRow = 303108
ActiveWindow.ScrollRow = 307501
ActiveWindow.ScrollRow = 301644
ActiveWindow.ScrollRow = 289930
ActiveWindow.ScrollRow = 270162
ActiveWindow.ScrollRow = 259912
ActiveWindow.ScrollRow = 241608
ActiveWindow.ScrollRow = 234287
ActiveWindow.ScrollRow = 220376
ActiveWindow.ScrollRow = 215251
ActiveWindow.ScrollRow = 206466
ActiveWindow.ScrollRow = 196948
ActiveWindow.ScrollRow = 194019
ActiveWindow.ScrollRow = 186698
ActiveWindow.ScrollRow = 180841
ActiveWindow.ScrollRow = 177180
ActiveWindow.ScrollRow = 171323
ActiveWindow.ScrollRow = 164001
ActiveWindow.ScrollRow = 159609
ActiveWindow.ScrollRow = 151555
ActiveWindow.ScrollRow = 146430
ActiveWindow.ScrollRow = 139841
ActiveWindow.ScrollRow = 136912
ActiveWindow.ScrollRow = 130323
ActiveWindow.ScrollRow = 123734
ActiveWindow.ScrollRow = 121537
ActiveWindow.ScrollRow = 115680
ActiveWindow.ScrollRow = 108359
ActiveWindow.ScrollRow = 104698
ActiveWindow.ScrollRow = 94448
ActiveWindow.ScrollRow = 89323
ActiveWindow.ScrollRow = 83466
ActiveWindow.ScrollRow = 82734
ActiveWindow.ScrollRow = 77609
ActiveWindow.ScrollRow = 76145
ActiveWindow.ScrollRow = 71752
ActiveWindow.ScrollRow = 68091
ActiveWindow.ScrollRow = 65895
ActiveWindow.ScrollRow = 60770
ActiveWindow.ScrollRow = 57841
ActiveWindow.ScrollRow = 52716
ActiveWindow.ScrollRow = 51252
ActiveWindow.ScrollRow = 46859
ActiveWindow.ScrollRow = 42466
ActiveWindow.ScrollRow = 38805
ActiveWindow.ScrollRow = 32948
ActiveWindow.ScrollRow = 28555
ActiveWindow.ScrollRow = 24895
ActiveWindow.ScrollRow = 21966
ActiveWindow.ScrollRow = 14645
ActiveWindow.ScrollRow = 10252
ActiveWindow.ScrollRow = 7323
ActiveWindow.ScrollRow = 4395
ActiveWindow.ScrollRow = 1466
ActiveWindow.ScrollRow = 2
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=100000"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13551615
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("J:J").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 8700771
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 8700771
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 8061142
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
Columns("K:K").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 = 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("L11").Select
End Sub
3b)
VBA Code to convert selected data into pdf from applicant data:
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
3c)
Check Spelling mistakes in the workbook using VBA Code
Public Sub check()
Sheet1.Cells.CheckSpelling
End Sub