A little bit of an "oops" in my code: change the SheetSelectionChange sub to
this:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

If UseMyFormatPainter Then
Selection.PasteSpecial xlPasteFormats
UseMyFormatPainter = False
Application.CutCopyMode = 0 ' turns "off" the dotted border around the
copied cell
End If

End Sub

Otherwise, it will always turn off CutCopyMode when you change the
selection, not a desirable behavior!

"K Dales" wrote:

When you copy a cell, you copy everything: contents and format. The
difference comes when pasting to a new cell, you specify you want to paste
the format. The "format painter" just automates this process. So your macro
would need to do the copy, have you specify the target cell(s), and then do a
PasteSpecial (this is what the format painter does, too). This means you
need some way of detecting when the user has chosen the cell to paste to, and
that makes it a bit more complex. You need to have a way to say (to Excel)
"I am waiting for a new cell..." and then when the user chooses a new cell,
do the paste.

There is a workbook event procedure for Workbook_SheetSelectionChange that
runs whenever the user changes the selection. So here is an outline of what
needs to happen:
1) User initiates your custom "format painter" macro
2) The macro copies the cell
3) The macro sets some sort of flag: waiting for next selection
4) The user changes the selection (activating Workbook_SheetSelectionChange)
5) the Workbook_SheetSelection procedure reads the flag that says waiting
for next selection, and recognizes that the user wants to paste the format.
So it runs code to do a PasteSpecial, then resets the flag (so it doesn't
keep pasting for every new selection)

Here is the whole deal: insert a module and use it for the code below:

Public UseMyFormatPainter as Boolean

Public Sub MyFormatPainter
Selection.Copy
UseMyFormatPainter = True
End Sub

Now go to ThisWorkbook in the Project Explorer and enter this code in the
Workbook:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

If UseMyFormatPainter Then Selection.PasteSpecial xlPasteFormats
UseMyFormatPainter = False
Application.CutCopyMode = 0 ' turns "off" the dotted border around the
copied cell

End Sub

This is bare-bones and does not do any error checking, or allow multiple
targets (like when you double-click the format painter) but it shows how to
control that kind of behavior. Hope it helps.

"JD" wrote:

I am trying to create a macro that when I press Ctrl+m, it would be as if I
am pressing the format painter icon. I tried recording a macro and each
time I press ctrl+m, it copies the cell, but not the format. Could someone
tell me how to create the macro to copy the format only of a cell?

Thanks Again as I am a newbie when it comes to macros/vba

JD