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 MultipleConditional FormattingExample()
Dim MyRange As Range
'Create range object
Set MyRange = Range("A1:DR307512”)
‘Delete previous conditional formats
MyRange.FormatConditions.Delete
‘Add first rule
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:= "=100", Formula2:= "-150"
MyRange.FormatConditions (1).Interior.Color= RGB(255,0,0)
‘Add second rule
MyRange. FormatConditions. Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:= "=100"
MyRange. FormatConditions (2). Interior.color = vbBlue
‘Add third rule
MyRange.FormatConditions.Add Type:=xlcellvalue. Operator:=xlGreater, _
Formula1:= "=150"
MyRange.FormatConditions (3). Interior.Color = vbYellow
‘Add fourth rule
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:= "=100"
MyRange.FormatConditions (2). Interior.color = vbBlue
End Sub
Create VBA Code to convert selected data into pdf from applicant data
Public Sub PDP ()
ThisWorkbook.Sheets (Array ("Sheet1", "Sheet2", "Sheet3")).Select
ActiveSheet.ExportAsPixedFormat _
Type:=xlTypePDF, _
Filename:= "C:\temp.pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:= True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
Check Spelling mistakes in the workbook using VBA Code
Public Function CheckItNew (r As Range) As Boolean
Dim MyText As String
MyText =r(1).Text
Dim ox1Ap As Object
Set ox1Ap=CreateObject("Excel.Application")
CheckItNew= oxlAp.checkSpelling (MyText)
Ox1Ap.Quit
Set ox1Ap = Nothing
End Function
https://docs.google.com/spreadsheets/d/18-r3l9v242f84xkA6uy5doEMFUrNcpRE/edit?usp=drive_link&ouid=109414232822990887670&rtpof=true&sd=true