Copying Cell Formats

[Guest]

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.

 

[Guest]

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)

 

[Guest]

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