Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
PROJECT LINKhttps://docs.google.com/spreadsheets/d/1LU4rY1WkEzTmFeqvjK2a4m6XSrIZlIKb/edit?usp=drive_link&ouid=109071902391416422223&rtpof=true&sd=true
https://docs.google.com/spreadsheets/d/1LU4rY1WkEzTmFeqvjK2a4m6XSrIZlIKb/edit?usp=drive_link&ouid=109071902391416422223&rtpof=true&sd=true
***************************************************************************************************************
- Create a macro for conditional formatting & freezing panes ?
Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+h
'
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.LargeScroll Down:=1
ActiveWindow.SmallScroll Down:=-63
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Range("Table1[[#Headers],[SK_ID_CURR]:[NAME_CONTRACT_TYPE]]").Select
Selection.AutoFill Destination:=Range("Table1[#Headers]"), Type:= _
xlFillDefault
Range("Table1[#Headers]").Select
ActiveWindow.SmallScroll Down:=15
Range("G19").Select
ActiveWindow.SmallScroll Down:=-72
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
ActiveWindow.SmallScroll Down:=-18
Columns("A:A").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With
With Selection.FormatConditions(1).BarColor
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillGradient
Selection.FormatConditions(1).Direction = xlContext
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderSolid
Selection.FormatConditions(1).NegativeBarFormat.BorderColorType = _
xlDataBarColor
With Selection.FormatConditions(1).BarBorder.Color
.Color = 13012579
.TintAndShade = 0
End With
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.BorderColor
.Color = 255
.TintAndShade = 0
End With
Range("H8").Select
End Sub
***************************************************************************************************************
Create VBA Code to convert selected data into pdf from applicant data?
Sub ExportSelectedDataToPDF()
Dim rng As Range
Dim newWorkbook As Workbook
Dim newWorksheet As Worksheet
' Set the range to the selected cells
Set rng = Selection
' Create a new workbook and copy the selected range
Set newWorkbook = Workbooks.Add
Set newWorksheet = newWorkbook.Sheets(1)
rng.Copy newWorksheet.Range("A1")
' Save the new workbook as PDF
Dim savePath As String
savePath = Application.GetSaveAsFilename(FileFilter:="PDF Files (*.pdf), *.pdf")
If savePath <> "False" Then
newWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath
newWorkbook.Close SaveChanges:=False
MsgBox "Data exported to PDF successfully!", vbInformation
Else
newWorkbook.Close SaveChanges:=False
MsgBox "Export to PDF canceled.", vbExclamation
End If
End Sub
**************************************************************************************************************
Check Spelling mistakes in the workbook using VBA Code ?
Sub SpellCheckWorkbook()
Dim ws As Worksheet
Dim cell As Range
' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Worksheets
' Loop through all cells with text in the worksheet
For Each cell In ws.
UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)
' Check spelling for each cell
If Not Application.CheckSpelling(word:=cell.Text) Then
cell.Interior.Color = RGB(255, 0, 0)
' Highlight misspelled cells
Else
cell.Interior.Color = RGB(255, 255, 255)
' Reset color for correctly spelled cells
End If
Next cell
Next ws
MsgBox "Spell check completed!", vbInformation
End Sub