Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
https://docs.google.com/spreadsheets/d/10mOZVICu0dY9Qy3tkX9VZXlj26u7-YL_/edit?usp=drive_link
*Create a macro for conditional formatting & freezing panes
Sub conditional_formatting()
'
' conditional_formatting Macro
' conditional formatting & freezing panes
'
'
ActiveCell.FormulaR1C1 = "S.NO"
Range("B1").Select
ActiveCell.FormulaR1C1 = "MATERIALS"
Range("C1").Select
ActiveCell.FormulaR1C1 = "QUANTITY"
Range("D1").Select
ActiveCell.FormulaR1C1 = "RATE"
Range("E1").Select
ActiveCell.FormulaR1C1 = "TOTAL"
Cells.Select
Selection.ColumnWidth = 21.44
Range("B1").Select
Columns("B:B").ColumnWidth = 42.89
Range("A2").Select
ActiveCell.FormulaR1C1 = "1"
Range("A3").Select
ActiveCell.FormulaR1C1 = "2"
Range("A2:A3").Select
Selection.AutoFill Destination:=Range("A2:A16"), Type:=xlFillDefault
Range("A2:A16").Select
Range("B2").Select
ActiveCell.FormulaR1C1 = "Bricks"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Cement"
Range("B2").Select
Selection.Copy
Range("B4").Select
ActiveSheet.Paste
Range("B6").Select
ActiveSheet.Paste
Range("B8").Select
ActiveSheet.Paste
Range("B10").Select
ActiveSheet.Paste
Range("B12").Select
ActiveSheet.Paste
Range("B14").Select
ActiveSheet.Paste
Range("B16").Select
ActiveSheet.Paste
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Range("B5").Select
ActiveSheet.Paste
Range("B7").Select
ActiveSheet.Paste
Range("B9").Select
ActiveSheet.Paste
Range("B11").Select
ActiveSheet.Paste
Range("B13").Select
ActiveSheet.Paste
Range("B15").Select
ActiveSheet.Paste
Range("C2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "1500"
Range("C3").Select
ActiveCell.FormulaR1C1 = "200"
Range("C3").Select
Selection.Copy
Range("C5").Select
ActiveSheet.Paste
Range("C7").Select
ActiveSheet.Paste
Range("C9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "300"
Range("C11").Select
ActiveCell.FormulaR1C1 = "300"
Range("C13").Select
ActiveCell.FormulaR1C1 = "100"
Range("C15").Select
ActiveCell.FormulaR1C1 = "200"
Range("C2").Select
Selection.Copy
Range("C4").Select
ActiveSheet.Paste
Range("C6").Select
ActiveSheet.Paste
Range("C8").Select
Application.CutCopyMode = False
Selection.Copy
Range("C6").Select
Range("C6").Select
Application.CutCopyMode = False
Selection.Copy
Range("C10").Select
ActiveSheet.Paste
Range("C14").Select
ActiveSheet.Paste
Range("C8").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "3000"
Range("C12").Select
ActiveCell.FormulaR1C1 = "2000"
Range("D2").Select
ActiveCell.Formula2R1C1 = "=IFS(RC[-2]=""Bricks"",10,RC[-2]=""Cement"",350)"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D15"), Type:=xlFillDefault
Range("D2:D15").Select
Range("E2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E15"), Type:=xlFillDefault
Range("E2:E15").Select
Range("E2:E15").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=50000"
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
Rows("1:1").Select
Selection.Font.Bold = True
Selection.Font.Size = 12
Selection.Font.Size = 14
Range("A1:E15").Select
Range("A1:E15").Select
Range("C16").Select
ActiveCell.FormulaR1C1 = "1500"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D16"), Type:=xlFillDefault
Range("D2:D16").Select
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E16"), Type:=xlFillDefault
Range("E2:E16").Select
Range("A1:E16").Select
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
Range("A1:E1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Range("D31").Select
End Sub
*Create VBA Code to convert selected data into pdf from applicant data
Public Sub SavetoPDF()
Dim ThisRng As Range
Dim strfile As String
Dim myfile As Variant
If Selection.Conut = 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
*Check Spelling mistakes in the workbook using VBA Code
Sub HighlightMissspelledCells()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange
If Not Application.CheckSpelling(rng.Text) Then
rng.Interior.ColorIndex = 6
End If
Next rng
End Sub