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

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.