BlogFor BusinessTalk to a counsellor
CoursesIIT ProgramsPlacementsLog in
BlogFor Business
Talk to a counsellor
Challenge

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

BBiswarup Das5 Jul 2023

Sub conditional_formatting_and_freeze_panes()
'
' conditional_formatting_and_freeze_panes Macro
'

'
Columns("A:B").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 96
ActiveWindow.ScrollColumn = 105
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 115
ActiveWindow.ScrollColumn = 116
ActiveWindow.ScrollColumn = 114
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Cells.Select
Selection.ColumnWidth = 33.67
ActiveWindow.SmallScroll ToRight:=-9
Selection.ColumnWidth = 26.13
Selection.ColumnWidth = 22.4
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With ActiveWorkbook.Sheets("Applicant_Data").Tab
.Color = 49407
.TintAndShade = 0
End With
With Selection.Font
.Name = "Calibri Light"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMajor
End With
Selection.Font.Bold = False
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Columns("D:D").Select
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlTextString, String:="CODE_GENDER", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.SmallScroll ToRight:=-3
Range("Table1[AMT_INCOME_TOTAL]").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=200000"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
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
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=600000"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=18
ActiveWindow.SmallScroll ToRight:=-3
ActiveWindow.SmallScroll Down:=51
ActiveWindow.SmallScroll ToRight:=5
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollRow = 371
ActiveWindow.ScrollRow = 1112
ActiveWindow.ScrollRow = 14449
ActiveWindow.ScrollRow = 23340
ActiveWindow.ScrollRow = 24081
ActiveWindow.ScrollRow = 25563
ActiveWindow.ScrollRow = 30008
ActiveWindow.ScrollRow = 31490
ActiveWindow.ScrollRow = 35936
ActiveWindow.ScrollRow = 36677
ActiveWindow.ScrollRow = 37047
ActiveWindow.ScrollRow = 36677
ActiveWindow.ScrollRow = 32972
ActiveWindow.ScrollRow = 30379
ActiveWindow.ScrollRow = 30749
ActiveWindow.ScrollRow = 31120
ActiveWindow.ScrollRow = 31861
ActiveWindow.ScrollRow = 32231
ActiveWindow.ScrollRow = 33713
ActiveWindow.ScrollRow = 34084
ActiveWindow.ScrollRow = 34454
ActiveWindow.ScrollRow = 35195
ActiveWindow.ScrollRow = 34824
ActiveWindow.ScrollRow = 33343
ActiveWindow.ScrollRow = 32972
ActiveWindow.ScrollRow = 33343
ActiveWindow.ScrollRow = 34084
ActiveWindow.ScrollRow = 34454
ActiveWindow.ScrollRow = 35195
ActiveWindow.ScrollRow = 36677
ActiveWindow.ScrollRow = 37047
ActiveWindow.ScrollRow = 37788
ActiveWindow.ScrollRow = 38159
ActiveWindow.ScrollRow = 38529
ActiveWindow.ScrollRow = 40381
ActiveWindow.ScrollRow = 41122
ActiveWindow.ScrollRow = 41863
ActiveWindow.ScrollRow = 42604
ActiveWindow.ScrollRow = 44457
ActiveWindow.ScrollRow = 44827
ActiveWindow.ScrollRow = 46309
ActiveWindow.ScrollRow = 46679
ActiveWindow.ScrollRow = 50014
ActiveWindow.ScrollRow = 50755
ActiveWindow.ScrollRow = 53718
ActiveWindow.ScrollRow = 55571
ActiveWindow.ScrollRow = 56312
ActiveWindow.ScrollRow = 55941
ActiveWindow.ScrollRow = 37788
ActiveWindow.ScrollRow = 1
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=200001", Formula2:="=599999"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=21
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("Table1[CODE_GENDER]").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="M", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 6736896
.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).Interior
.PatternColorIndex = xlAutomatic
.Color = 16711935
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("Table1[FLAG_OWN_CAR]").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="N", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
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
Selection.FormatConditions.Add Type:=xlTextString, String:="Y", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("Table1[FLAG_OWN_REALTY]").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Y", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="N", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
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 = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range("Table1[NAME_INCOME_TYPE]").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Working", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"Commercial associate", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=-9
Selection.FormatConditions.Add Type:=xlTextString, String:="Unemployed", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=117
ActiveWindow.SmallScroll ToRight:=-2
ActiveWindow.SmallScroll Down:=87
ActiveWindow.SmallScroll ToRight:=-7
ActiveWindow.SmallScroll Down:=45
ActiveWindow.SmallScroll ToRight:=-15
ActiveWindow.SmallScroll Down:=444
ActiveWindow.SmallScroll ToRight:=-6
ActiveWindow.SmallScroll Down:=-312
ActiveWindow.SmallScroll ToRight:=9
Columns("M:M").Select
Range("M394").Activate
Selection.FormatConditions.Add Type:=xlTextString, String:="Pensioner", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=18
Selection.FormatConditions.Add Type:=xlTextString, String:="State servant" _
, TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=24
ActiveWindow.ScrollRow = 371
ActiveWindow.ScrollRow = 741
ActiveWindow.ScrollRow = 1112
ActiveWindow.ScrollRow = 1482
ActiveWindow.ScrollRow = 2223
ActiveWindow.ScrollRow = 2594
ActiveWindow.ScrollRow = 4076
ActiveWindow.ScrollRow = 4817
ActiveWindow.ScrollRow = 5187
ActiveWindow.ScrollRow = 5558
ActiveWindow.ScrollRow = 8892
ActiveWindow.ScrollRow = 12967
ActiveWindow.ScrollRow = 22969
ActiveWindow.ScrollRow = 26304
ActiveWindow.ScrollRow = 32231
ActiveWindow.ScrollRow = 32602
ActiveWindow.ScrollRow = 37418
ActiveWindow.ScrollRow = 37788
ActiveWindow.ScrollRow = 41122
ActiveWindow.ScrollRow = 43345
ActiveWindow.ScrollRow = 45198
ActiveWindow.ScrollRow = 45568
ActiveWindow.ScrollRow = 47791
ActiveWindow.ScrollRow = 48532
ActiveWindow.ScrollRow = 51866
ActiveWindow.ScrollRow = 52977
ActiveWindow.ScrollRow = 53348
ActiveWindow.ScrollRow = 54830
ActiveWindow.ScrollRow = 56312
ActiveWindow.ScrollRow = 59275
ActiveWindow.ScrollRow = 60757
ActiveWindow.ScrollRow = 61498
ActiveWindow.ScrollRow = 62610
ActiveWindow.ScrollRow = 65573
ActiveWindow.ScrollRow = 69648
ActiveWindow.ScrollRow = 72983
ActiveWindow.ScrollRow = 75205
ActiveWindow.ScrollRow = 76687
ActiveWindow.ScrollRow = 80762
ActiveWindow.ScrollRow = 82244
ActiveWindow.ScrollRow = 87060
ActiveWindow.ScrollRow = 89654
ActiveWindow.ScrollRow = 98915
ActiveWindow.ScrollRow = 102990
ActiveWindow.ScrollRow = 104843
ActiveWindow.ScrollRow = 107066
ActiveWindow.ScrollRow = 107807
ActiveWindow.ScrollRow = 108177
ActiveWindow.ScrollRow = 115586
ActiveWindow.ScrollRow = 116698
ActiveWindow.ScrollRow = 117068
ActiveWindow.ScrollRow = 118180
ActiveWindow.ScrollRow = 120032
ActiveWindow.ScrollRow = 122255
ActiveWindow.ScrollRow = 122996
ActiveWindow.ScrollRow = 125219
ActiveWindow.ScrollRow = 127441
ActiveWindow.ScrollRow = 127812
ActiveWindow.ScrollRow = 129664
ActiveWindow.ScrollRow = 134851
ActiveWindow.ScrollRow = 136333
ActiveWindow.ScrollRow = 138185
ActiveWindow.ScrollRow = 138555
ActiveWindow.ScrollRow = 144853
ActiveWindow.ScrollRow = 146706
ActiveWindow.ScrollRow = 148187
ActiveWindow.ScrollRow = 151522
ActiveWindow.ScrollRow = 153374
ActiveWindow.ScrollRow = 160413
ActiveWindow.ScrollRow = 161154
ActiveWindow.ScrollRow = 166711
ActiveWindow.ScrollRow = 167822
ActiveWindow.ScrollRow = 173379
ActiveWindow.ScrollRow = 176713
ActiveWindow.ScrollRow = 182641
ActiveWindow.ScrollRow = 185605
ActiveWindow.ScrollRow = 190050
ActiveWindow.ScrollRow = 193014
ActiveWindow.ScrollRow = 200794
ActiveWindow.ScrollRow = 204499
ActiveWindow.ScrollRow = 210426
ActiveWindow.ScrollRow = 210796
ActiveWindow.ScrollRow = 209685
ActiveWindow.ScrollRow = 176343
ActiveWindow.ScrollRow = 49273
ActiveWindow.ScrollRow = 6669
ActiveWindow.ScrollRow = 1
Columns("O:O").Select
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"Single / not married", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="Married", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:= _
"Single / not married", TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16752384
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 13561798
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
ActiveWindow.SmallScroll Down:=18
ActiveWindow.SmallScroll ToRight:=-6
ActiveWindow.SmallScroll Down:=-24
ActiveWindow.SmallScroll ToRight:=-19
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("B2").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll ToRight:=-15
ActiveWindow.SmallScroll Down:=-42
End Sub

Public Sub ConverttoPDF()
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

Public Sub SpellCheck()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If Not Application.CheckSpelling(word:=cell.Text) Then
cell.Style = "Bad"
End If
Next cell
End Sub

Excel file link:
https://drive.google.com/drive/folders/1ccjfLOgDjfZBe-QbZ4G9gv6b-9xZG4wr?usp=sharing

Your portfolio, next

Ship work like this
in your own course.

Talk to a career counsellor

Free 20-minute call. We'll map the right course to where you want to work.

By submitting you agree to our Terms and Privacy Policy.