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

link-https://drive.google.com/drive/folders/1JSoiObVEEHofFpD-0PSJPss8TYduKVBc?usp=sharing

• Create a macro for conditional formatting & freezing panes

Sub freezeing_pane()
'
' freezeing_pane Macro
'

'
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Columns("H:H").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=100000"
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("L:L").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="family", _
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("AO:AO").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="business", _
TextOperator:=xlContains
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
Columns("I:I").Select
Selection.FormatConditions.AddAboveAverage
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).AboveBelow = xlAboveAverage
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
Range("L10").Select
End Sub

2.

Sub ExportSelectedDataToPDF()
Dim rng As Range
Dim newWorkbook As Workbook
Dim filePath As String

' Set the range to be exported as PDF
Set rng = Selection


If rng Is Nothing Then
MsgBox "Please select the data to export as PDF.", vbExclamation
Exit Sub
End If

' Create a new workbook and copy the selected data
Set newWorkbook = Workbooks.Add
rng.Copy newWorkbook.Sheets(1).Range("A1")

' Save the new workbook as PDF
filePath = "C:\Users\Admin\OneDrive\Documents\file.pdf"
newWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard

' Close and clean up
newWorkbook.Close SaveChanges:=False
Set newWorkbook = Nothing

MsgBox "Selected data has been exported as PDF.", vbInformation
End Sub

3.

Sub CheckSpelling()
Dim ws As Worksheet
Dim rng As Range

For Each ws In ThisWorkbook.Worksheets
' Set the range to the used range of the worksheet
Set rng = ws.UsedRange


rng.CheckSpelling
Next ws

MsgBox "Spell check completed.", vbInformation
End Sub