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

Google Drive Link:

https://drive.google.com/drive/folders/1UXSuXvTeu-zqYfw3-N4Hmz8FEmQZOwMg?usp=sharing

For creating macros for conditional formatting, we need to first create dummy cells that we apply conditional formatting on. Once we done the desired conditional formatting on them, we start recording the macro. We give a name for the macro, say "Conditional_Formatting" and click "OK". After that, copy the dummy cells that we applied conditional formatting on, and select the range of cells you want the macro to record the conditional formatting. Right click on the selected range cells, and press Paste Specials. Once we click that, select "Formats" option and click "OK". Once this is done, stop the recording. The macro for conditional formatting is done.

For freezing panes, we do the same step; start recording a new macro under the name "Freeze_panes" and once that's done, go to View->Freeze panes in window ribbon. Stop recording once we completed the freezing pane process.

VBA Code to convert selected data into pdf:

Public Sub SaveAsPdf()

Dim w As Worksheet

Dim Fname As String

Dim Fpath As String

Set w = ActiveSheet

Fname = "Applicant Data PDF"

w.PageSetup.Orientation = xlPortrait

Fpath = ThisWorkbook.Path

w.ExportAsFixedFormat Type:= xlTypePDF, FileName:= FilePath & "\" & FileName & ".pdf", _Quality:= xlQualityStandard, InclueDocProperties:= True, IgnorePrintAreas:= False

MsgBox("Convertion done!")

End Sub

VBA code to check Spelling mistakes in the workbook:

Public Sub Check_Spelling()

For Each x In Worksheets

Sheets(x.Name).Cells.CheckSpelling

Next

End Sub