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

onedrive link: Applicant Data.xlsx

https://1drv.ms/x/s!At6dDzbtw1hlg0kbBcNDBTBf-7ib?e=kE6VwB

1.Create a macro for data formatting (conditional formatting, freezing panes)

Sub data_formatting()

' data_formatting Macro
' conditional formatting, feezing panes

With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Columns("F:F").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="n", _
TextOperator:=xlContains
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("D:D").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="f", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("F1").Select
Selection.FormatConditions.Delete
End Sub

2. Create VBA Code to convert selected data into pdf from applicant data

Public Sub PDF()
ThisWorkbook.Sheets(Array("Dashboard")).Select

ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="Sample Excel File Saved As PDF", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

End Sub

3. Check Spelling mistakes in the workbook using VBA Code

Sub Highlight_Misspelled_Words()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If Not Application.CheckSpelling(Word:=cell.Text) Then cell.Interior.ColorIndex = 6
Next
End Sub