Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
Dashboard -
The file will be attached named "Applicant Data - Solution"
The Separate Worksheet is also attached, named " ". All three data summary in the same file. Due to data, the file is LARGE.
VBA Macros -
1.
Sub ApplyConditionalFormattingAndFreezePanes()
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Cells.FormatConditions.Delete
Set rng = ws.Range("A1:D10")
With rng.FormatConditions.Add(Type:=xlExpression, Formula1:="=$A1>10")
.Interior.Color = RGB(255, 0, 0)
End With
ws.Range("B2").Select
ws.Activate
ActiveWindow.FreezePanes = True
End Sub
2.
Sub ConvertSelectionToPDF()
Dim ws As Worksheet
Dim rng As Range
Dim savePath As String
Dim fileName As String
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = Selection
If rng Is Nothing Then
MsgBox "Please select the data to convert to PDF.", vbExclamation
Exit Sub
End If
With Application.FileDialog(msoFileDialogSaveAs)
.Title = "Save PDF"
.Filter = "PDF files (*.pdf)|*.pdf"
If .Show = -1 Then
savePath = .SelectedItems(1)
If Right(savePath, 4) <> ".pdf" Then
savePath = savePath & ".pdf"
End If
rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath
fileName = Mid(savePath, InStrRev(savePath, "\") + 1)
MsgBox "The selected data has been converted to PDF and saved as '" & fileName & "'.", vbInformation
End If
End With
End Sub
3.
Sub CheckSpelling()
Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.UsedRange
Application.DisplayAlerts = False
For Each cell In rng
If cell.Value <> "" Then
cell.CheckSpelling
End If
Next cell
Application.DisplayAlerts = True
MsgBox "Spelling check complete.", vbInformation
End Sub