Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
- Create a macro for conditional formatting & freezing panes
Sub ApplyConditionalFormattingAndFreezePanes()
Dim ws As Worksheet
Dim rng As Range
Dim panesRange As Range
' Set the worksheet object to the desired worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
' Set the range to apply conditional formatting
Set rng = ws.Range("A1:F10")
' Apply conditional formatting to the range
rng.FormatConditions.AddColorScale ColorScaleType:=3
rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
rng.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
rng.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
rng.FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
rng.FormatConditions(1).ColorScaleCriteria(2).FormatColor.Color = RGB(255, 255, 0)
rng.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
rng.FormatConditions(1).ColorScaleCriteria(3).FormatColor.Color = RGB(0, 255, 0)
rng.FormatConditions(1).ColorScaleCriteria(3).FormatColor.TintAndShade = 0
' Set the range for freezing panes
Set panesRange = ws.Range("A2")
' Freeze panes
ws.Activate
panesRange.Select
ActiveWindow.FreezePanes = True
' Adjust the scroll position to the top-left of the range
ws.Application.Goto panesRange, Scroll:=True
' Clear the clipboard to prevent flashing marching ants around the range
Application.CutCopyMode = False
End Sub
- Create VBA Code to convert selected data into pdf from applicant data
Sub ConvertSelectedDataToPDF()
Dim rng As Range
Dim newWB As Workbook
Dim newWS As Worksheet
Dim filePath As String
' Set the range to the currently selected data
Set rng = Selection
' Check if any data is selected
If rng Is Nothing Then
MsgBox "No data selected.", vbExclamation
Exit Sub
End If
' Create a new workbook and worksheet to copy the selected data
Set newWB = Workbooks.Add
Set newWS = newWB.Sheets(1)
' Copy the selected data to the new worksheet
rng.Copy newWS.Range("A1")
' Set the file path for the PDF file
filePath = "D:\Rehan\REHAN"
' Export the new workbook as a PDF
newWB.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath
' Close the new workbook without saving
newWB.Close SaveChanges:=False
' Inform the user about the successful conversion
MsgBox "Data converted to PDF successfully!", vbInformation
End Sub
- Check Spelling mistakes in the workbook using VBA Code
Sub CheckSpellingInWorkbook()
Dim ws As Worksheet
Dim rng As Range
' Loop through each worksheet in the active workbook
For Each ws In ThisWorkbook.Worksheets
' Set the range to check spelling within the used range of the worksheet
Set rng = ws.UsedRange
' Check spelling for the range
rng.CheckSpelling
Next ws
End Sub
LINK OF FOOGLE DRIVE: