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/file/d/1XV6RYo_rcBUO9JTfr9rmKj7cWsISFt5y/view?usp=sharing
https://drive.google.com/file/d/1XV6RYo_rcBUO9JTfr9rmKj7cWsISFt5y/view?usp=share_link
In the project for IJS Bank Limited, I served as a Business Analyst to analyze loan applicant data. I created an Excel dashboard with pivot charts to visualize gender ratios, ownership statistics, and client demographics. Additionally, I implemented VBA code for conditional formatting, data-to-PDF conversion, and spelling checks to enhance data reporting and usability.
- VBA code is not working in my system so i have shared my VBA code in macros and text box
Sub SpellCheckActiveSheet() Dim ws As Worksheet Dim cell As Range For Each ws In ThisWorkbook.Sheets ws.Activate For Each cell In ws.UsedRange If Not Application.CheckSpelling(word:=cell.Text) Then cell.Interior.Color = RGB(255, 0, 0) End If Next cell Next ws End Sub
Sub ExportDataToPDF() Dim MyRange As Range Dim MyFileName As String Set MyRange = ThisWorkbook.Sheets("Applicant Data").Range("A1:DR307512") MyFileName = "ApplicantData.pdf" MyRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=MyFileName, Quality:=xlQualityStandard, OpenAfterPublish:=True End Sub
Sub Macro_Project2()
'
' Macro_Project2 Macro
' Conditional Formatting and Freezing Panes
'
'
Columns("C:C").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Cash Loans", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249946592608417
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"Revolving loans", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0.349986266670736
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("D:D").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="m", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -4165632
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("D:D").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="F", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -6279056
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.14996795556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Rows("1:1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End Sub
Sub Macro_Project22()
'
' Macro_Project22 Macro
' Conditional Formatting and Freezing Panes
'
'
Columns("C:C").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Cash loans", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorAccent6
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("C:C").Select
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"Revolving Loans", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.499984740745262
End With
Selection.FormatConditions(1).StopIfTrue = False
Columns("D:D").Select
ActiveWindow.Zoom = 148
ActiveWindow.Zoom = 72
Selection.FormatConditions.Add Type:=xlTextString, String:="M", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -4165632
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="F", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.Color = -6279056
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Rows("1:1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
End Sub