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

https://drive.google.com/drive/folders/1irzWUpl4zGE18aY33VOqcS_MC7pLL1Cf?usp=share_link

DATA SUMMARY FOR FEMALE LOAN APPLICANTS FOR INCOME AND LOAN AMOUNTS
AMOUNT_INCOME_TOTAL	31,58,84,28,942.41
AMOUNT_CREDIT_TOTAL	1,20,00,44,36,385.00
CASH_LOANS_TOTAL	1,13,90,95,01,385.00
REVOLVING_LOANS_TOTAL	6,09,49,35,000.00

DATA SUMMARY FOR MALE LOAN APPLICANTS FOR INCOME AND LOAN AMOUNTS
AMOUNT_INCOME_TOTAL	20,31,80,41,018.53
AMOUNT_CREDIT_TOTAL	64,20,10,50,310.50
CASH_LOANS_TOTAL	60,81,06,60,310.50
REVOLVING_LOANS_TOTAL	3,39,03,90,000.00

DATA SUMMARY FOR DIFFERENT FLAG DOCUMENTS AND THEIR VARIANTS
Flag_document	Dcount for 1	Dcount for 0
FLAG_DOCUMENT_2	13	307498
FLAG_DOCUMENT_3	218340	89171
FLAG_DOCUMENT_4	25	307486
FLAG_DOCUMENT_5	4648	302863
FLAG_DOCUMENT_6	27078	280433
FLAG_DOCUMENT_7	59	307452
FLAG_DOCUMENT_8	25024	282487
FLAG_DOCUMENT_9	1198	306313
FLAG_DOCUMENT_10	7	307504
FLAG_DOCUMENT_11	1203	306308
FLAG_DOCUMENT_12	2	307509
FLAG_DOCUMENT_13	1084	306427
FLAG_DOCUMENT_14	903	306608
FLAG_DOCUMENT_15	372	307139
FLAG_DOCUMENT_16	3053	304458
FLAG_DOCUMENT_17	82	307429
FLAG_DOCUMENT_18	2500	305011
FLAG_DOCUMENT_19	183	307328
FLAG_DOCUMENT_20	156	307355
FLAG_DOCUMENT_21	103	307408

3)

A) Macro for conditional formating and freeze panes

Sub Conditional_Freeze()
'
' Conditional_Freeze Macro
'

'
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 10
.Percent = True
End With
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("H:H").Select
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 10
.Percent = True
End With
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
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
Columns("J:J").Select
Selection.FormatConditions.AddTop10
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.TopBottom = xlTop10Top
.Rank = 10
.Percent = True
End With
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
Range("B2").Select
ActiveWindow.FreezePanes = False
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll Down:=-15
ActiveWindow.SmallScroll ToRight:=0
End Sub

B) VBA Code to convert selected data into pdf from applicant data

Public Sub Print_to_PDF()
Dim ThisRng As Range
Dim strfile As String
Dim myfile As Variant

If Selection.Count = 1 Then
Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)
Else
Set ThisRng = Selection
End If
strfile = "Selection" & "_" _
& Format(Now(), "yyyymmdd_hhmmss") _
& ".pdf"
strfile = ThisWorkbook.Path & "\" & strfile

myfile = Application.GetSaveAsFilename _
(InitialFileName:=strfile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save as PDF")

If myfile <> "False" Then
ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
End If
End Sub

C) Spelling mistakes in the workbook using VBA Code

Public Sub Spell_check()
Dim Rng As Range
For Each Rng In ActiveSheet.UsedRange
If Not Application.CheckSpelling(Rng.Text) Then
Rng.Interior.Color = RGB(255, 0, 0)
End If
Next Rng
End Sub