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

1. Create a Macro for Data Formatting (Conditional Formatting, Freezing Panes) of workbook as same will be applied to the data of next quarter

• Sub DataFormatting()
  '
  ' DataFormatting Macro
  ' Macro for Data Formatting (Conditional Formatting, Freezing Panes)
  '
  ' Keyboard Shortcut: Ctrl+k
  '
  Range("Applicant_Data[[#All],[AMT_INCOME_TOTAL]]").Select
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
  Formula1:="=540000"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Color = -16754788
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 10284031
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
  Formula1:="=220000"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Color = -16752384
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 13561798
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  ActiveWindow.SmallScroll Down:=-115
  ActiveWindow.ScrollRow = 307484
  ActiveWindow.ScrollRow = 307033
  ActiveWindow.ScrollRow = 304782
  ActiveWindow.ScrollRow = 300731
  ActiveWindow.ScrollRow = 251659
  ActiveWindow.ScrollRow = 143613
  ActiveWindow.ScrollRow = 118852
  ActiveWindow.ScrollRow = 90490
  ActiveWindow.ScrollRow = 1
  Range("E3").Select
  ActiveWindow.FreezePanes = True
  Range("G9").Select
  End Sub
• 2.Create VBA Code to convert selected data into pdf from applicant data
  Sub PrintSelectionToPDF()
  Dim ThisRng As Range
  Dim strfile As String
  Dim myfile As Variant
  If Selection.Count = 1 Then
  Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)
  Else
  Set ThisRng = Selection
  End If
  'Prompt for save location
  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 'save as PDF
  ThisRng.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

3.Check Spelling mistakes in the workbook using VBA Code

Public Sub HighlightSpellingMistakes()

Dim wd As Range

For Each wd In ActiveSheet.UsedRange

If Not Application.CheckSpelling(word:=wd.Text) Then

wd.Font.Color = vbRed

wd.Interior.Color = vbYellow

End If

Next wd

End Sub

answer for first two question link

https://drive.google.com/drive/folders/1V2LEoDCUXZ1s4E3m2m-_q1j4oUeOvH9C?usp=share_link