Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
1. VBA Macro for Conditional Formatting & Freezing Panes
Ans:
Sub ApplyConditionalFormattingAndFreezePanes()
Dim ws As Worksheet
Set ws = ActiveSheet
' Apply Conditional Formatting (Example: Highlight Loan Amount > 50000)
Dim rng As Range
Set rng = ws.Range("D2:D1000") ' Modify the range based on your dataset
With rng.FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="50000")
.Interior.Color = RGB(255, 0, 0) ' Red color for highlight
End With
' Freeze Panes at row 1
ws.Range("A2").Select
ActiveWindow.FreezePanes = True
MsgBox "Conditional Formatting and Freeze Panes Applied!", vbInformation, "Done"
End Sub
2. VBA Code to Convert Selected Data into PDF
Ans:
Sub ExportToPDF()
Dim ws As Worksheet
Dim rng As Range
Dim filePath As String
' Set worksheet
Set ws = ActiveSheet
' Select the range of data to be exported
Set rng = ws.UsedRange
' Define file path for saving the PDF
filePath = ThisWorkbook.Path & "\Loan_Report.pdf"
' Export the selection to PDF
rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard, _IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
MsgBox "PDF has been created successfully at: " & filePath, vbInformation, "Export Complete"
End Sub
3. VBA Code to Check Spelling Mistakes
Ans:
Sub CheckSpelling()
Dim ws As Worksheet
Dim rng As Range
' Loop through all sheets in the workbook
For Each ws In ActiveWorkbook.Sheets
Set rng = ws.UsedRange
rng.CheckSpelling
Next ws
MsgBox "Spelling check completed for all worksheets!", vbInformation, "Spell Check"
End Sub