July 21, 2018

Automate Format Painter

How to automate Format Painter using VBA. We can apply the same formatting to specific ranges based on a formatting in another worksheet range. If we can automate the process then highlighting relevant data becomes quick and easy. watch the video before studying the VBA code:

Watch this video on YouTube. Also watch this video on YouTube.

Here’s the complete VBA code including the code to clear all formats before applying the Format Painter:

Sub applyFormatPainter()
Dim myrange As Range
Dim lastrow As Long, lastcolumn As Long, I As Long, mymaxvalue As Long

lastrow = Sheets(“Sheet1”).Range(“A” & Rows.count).End(xlUp).Row
lastcolumn = ActiveSheet.Cells(1, Columns.count).End(xlToLeft).Column

Range(Cells(2, 1), Cells(lastrow, lastcolumn)).ClearFormats

Watch this video on YouTube.


For I = 2 To lastcolumn
Set myrange = ActiveSheet.Range(Cells(2, I), Cells(lastrow, I))
mymaxvalue = Application.WorksheetFunction.Max(myrange)
a = Selection.Find(What:=mymaxvalue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Address
Range(a).PasteSpecial Paste:=xlPasteFormats

Next I

Application.CutCopyMode = False
End Sub