Project 2 - Data Reporting of Supply Chain Management companies

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

LINK FOR THE GOOGLEDRIVE:

https://drive.google.com/file/d/1sB0BN3O4Ts6u6JIkVMaGXU2enjXx0MPC/view?usp=sharing

https://drive.google.com/file/d/1sB0BN3O4Ts6u6JIkVMaGXU2enjXx0MPC/view?usp=drive_link

-------------------------------------------------------------------------------------------------------------------------------------

3.1Create a macro for conditional formatting & freezing panes

Sub ApplyConditionalFormattingAndFreezePanes()
' Applying Conditional Formatting
With ActiveSheet.Range("A1:DR307512") ' Change the range as per your requirement
.FormatConditions.Add Type:=xlExpression, Formula1:="=$C1=""Pending""" ' Change the condition as per your requirement
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = RGB(255, 0, 0) ' Change the color as per your requirement
.TintAndShade = 0
End With
.FormatConditions(1).StopIfTrue = False
End With

' Freezing Panes
With ActiveWindow
.SplitColumn = 1 ' Change the column number where you want to freeze panes
.SplitRow = 1 ' Change the row number where you want to freeze panes
.FreezePanes = True
End With
End Sub

3.2 Create VBA Code to convert selected data into pdf from applicant data

Sub ConvertToPDF()
Dim ws As Worksheet
Dim rng As Range
Dim filePath As String

Set ws = ActiveSheet ' Change to the desired worksheet

Set rng = Selection ' Change to the desired range

With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save PDF"
.InitialFileName = "ApplicantData.pdf" ' Change the default filename if desired
If .Show = -1 Then
filePath = .SelectedItems(1)
Else
Exit Sub ' User canceled, exit the macro
End If
End With

' Export the range as a PDF file
rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=filePath, Quality:=xlQualityStandard

MsgBox "PDF created successfully!"
End Sub

3.3 Check Spelling mistakes in the workbook using VBA Code

Sub HighlightErrors()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range

Set ws = ActiveSheet ' Change to the desired worksheet

' Set the range to check for errors (e.g., entire worksheet)
Set rng = ws.UsedRange ' Change to the desired range

' Loop through each cell in the range
For Each cell In rng
' Check if the cell contains an error value
If IsError(cell.Value) Then
cell.Interior.Color = RGB(255, 0, 0) ' Highlight the cell with red color
End If
Next cell

MsgBox "Error highlighting completed!"
End Sub

https://docs.google.com/spreadsheets/d/108Wg1aFafEHe2et-DCSVBrJGaU5HVuqe/edit?usp=drive_link&ouid=110444707889704177765&rtpof=true&sd=true