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

LINK :

https://drive.google.com/drive/u/1/folders/1lb10tlKafdrHsdi8Rf17UjyP9utHYE75

Macros Code for conditional Formatting and Freeze Panes

Sub Macro1()

'

' Macro1 Macro

' Conditional Formatting and freeze Panes

'

'

ActiveWindow.ScrollColumn = 103

ActiveWindow.ScrollColumn = 101

ActiveWindow.ScrollColumn = 96

ActiveWindow.ScrollColumn = 91

ActiveWindow.ScrollColumn = 86

ActiveWindow.ScrollColumn = 82

ActiveWindow.ScrollColumn = 78

ActiveWindow.ScrollColumn = 74

ActiveWindow.ScrollColumn = 70

ActiveWindow.ScrollColumn = 67

ActiveWindow.ScrollColumn = 64

ActiveWindow.ScrollColumn = 61

ActiveWindow.ScrollColumn = 58

ActiveWindow.ScrollColumn = 56

ActiveWindow.ScrollColumn = 54

ActiveWindow.ScrollColumn = 52

ActiveWindow.ScrollColumn = 50

ActiveWindow.ScrollColumn = 49

ActiveWindow.ScrollColumn = 48

ActiveWindow.ScrollColumn = 47

ActiveWindow.ScrollColumn = 46

ActiveWindow.ScrollColumn = 45

ActiveWindow.ScrollColumn = 44

ActiveWindow.ScrollColumn = 43

ActiveWindow.ScrollColumn = 42

ActiveWindow.ScrollColumn = 41

ActiveWindow.ScrollColumn = 40

ActiveWindow.ScrollColumn = 39

ActiveWindow.ScrollColumn = 38

ActiveWindow.ScrollColumn = 37

ActiveWindow.ScrollColumn = 35

ActiveWindow.ScrollColumn = 34

ActiveWindow.ScrollColumn = 33

ActiveWindow.ScrollColumn = 32

ActiveWindow.ScrollColumn = 31

ActiveWindow.ScrollColumn = 30

ActiveWindow.ScrollColumn = 29

ActiveWindow.ScrollColumn = 28

ActiveWindow.ScrollColumn = 27

ActiveWindow.ScrollColumn = 26

ActiveWindow.ScrollColumn = 25

ActiveWindow.ScrollColumn = 24

ActiveWindow.ScrollColumn = 23

ActiveWindow.ScrollColumn = 22

ActiveWindow.ScrollColumn = 21

ActiveWindow.ScrollColumn = 20

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 = 7

ActiveWindow.ScrollColumn = 5

ActiveWindow.ScrollColumn = 3

ActiveWindow.ScrollColumn = 2

ActiveWindow.ScrollColumn = 1

Range("A1:DR307512").Select

Selection.Copy

Sheets.Add After:=ActiveSheet

ActiveSheet.Paste

Rows("1:1").Select

With ActiveWindow

.SplitColumn = 0

.SplitRow = 1

End With

ActiveWindow.FreezePanes = True

ActiveWindow.ScrollRow = 495

ActiveWindow.ScrollRow = 1482

ActiveWindow.ScrollRow = 1976

ActiveWindow.ScrollRow = 2469

ActiveWindow.ScrollRow = 5431

ActiveWindow.ScrollRow = 5924

ActiveWindow.ScrollRow = 6418

ActiveWindow.ScrollRow = 6911

ActiveWindow.ScrollRow = 7405

ActiveWindow.ScrollRow = 9379

ActiveWindow.ScrollRow = 9873

ActiveWindow.ScrollRow = 10366

ActiveWindow.ScrollRow = 10860

ActiveWindow.ScrollRow = 11353

ActiveWindow.ScrollRow = 11847

ActiveWindow.ScrollRow = 12340

ActiveWindow.ScrollRow = 12834

ActiveWindow.ScrollRow = 13327

ActiveWindow.ScrollRow = 13821

ActiveWindow.ScrollRow = 13327

ActiveWindow.ScrollRow = 10860

ActiveWindow.ScrollRow = 6911

ActiveWindow.ScrollRow = 2

Range("H2").Select

Range(Selection, Selection.End(xlDown)).Select

Application.CutCopyMode = False

Cells.FormatConditions.Delete

Range("H2:H307512").Select

Selection.FormatConditions.AddColorScale ColorScaleType:=3

Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _

xlConditionValueNumber

Selection.FormatConditions(1).ColorScaleCriteria(1).Value = 50000

With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor

.Color = 7039480

.TintAndShade = 0

End With

Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _

xlConditionValueNumber

Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 150000

With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor

.Color = 8711167

.TintAndShade = 0

End With

Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _

xlConditionValueNumber

Selection.FormatConditions(1).ColorScaleCriteria(3).Value = 250000

With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor

.Color = 8109667

.TintAndShade = 0

End With

End Sub

Q2. Create VBA Code to create selected data into PDF

Sub PrintSelectionToPDF()

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 = "VBA selected Data to PDF" & "_" _

& Format(Now(), "yyyymmdd_hhmmss") _

& ".pdf"

strfile = ThisWorkbook.Path & "\" & strfile

myfile = Application.GetSaveAsFilename _

(InitialFileName:=strfile, _

FileFilter:="PDF Files (*.pdf), *.pdf", _

Title:="Applicant Data")

If myfile <> "False" Then

ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _

myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _

IgnorePrintAreas:=False, OpenAfterPublish:=True

Else

End If

End Sub

Q. Checking Spelling mistakes in the workbook

Public Sub SpellCheck()

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