Looking for VBA coding to copy just the fill color and font format from one
cell into another. I tried using the macro recorder and the Copy Format
button (the paint brush), but that copies the borders and everything. The
resulting code was:

Range("N27:Q53").Select
Selection.Copy
Range("O27").Select
ActiveSheet.PasteSpecial Format:=4, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

I assume the ".PasteSpecial Format:=4" part means to paste all formats. I
want just the fill color and font characteristics. Would appreciate any
help. Thanks.

Hi Mark
AFAIK this is not possible using the PasteSpecial method (in your case
'Format' specifies the 'clipboard' format which should be used - for more
see the VBA help)

"Mark Schreiber" wrote:

Looking for VBA coding to copy just the fill color and font format from one
cell into another. I tried using the macro recorder and the Copy Format
button (the paint brush), but that copies the borders and everything. The
resulting code was:

Range("N27:Q53").Select
Selection.Copy
Range("O27").Select
ActiveSheet.PasteSpecial Format:=4, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

I assume the ".PasteSpecial Format:=4" part means to paste all formats. I
want just the fill color and font characteristics. Would appreciate any
help. Thanks.

Hi, Frank.
Thanks for the quick response. Looks like that path does not lead to
success. A possible alternative approach: Is there a way to use VBA to look
at a particular cell and "read" its formatting? For example, the following
code snippet would paint cell D7 red:
Range("D7").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With

So is there a VBA method to go to cell D7, have it "read" the formatting to
see that its ColorIndex value is 3, store that value in a variable, then
carry that value into another section of code that will insert the variable
value "3" into the ColorIndex property of another cell, say into E7?

Mark

"Frank Kabel" wrote:

Hi Mark
AFAIK this is not possible using the PasteSpecial method (in your case
'Format' specifies the 'clipboard' format which should be used - for more
see the VBA help)

"Mark Schreiber" wrote:

Looking for VBA coding to copy just the fill color and font format from one
cell into another. I tried using the macro recorder and the Copy Format
button (the paint brush), but that copies the borders and everything. The
resulting code was:

Range("N27:Q53").Select
Selection.Copy
Range("O27").Select
ActiveSheet.PasteSpecial Format:=4, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

I assume the ".PasteSpecial Format:=4" part means to paste all formats. I
want just the fill color and font characteristics. Would appreciate any
help. Thanks.