Copy Colors but not conditional formatting

Question

Please help me to understand how to Copy Colors from a specific cell but not conditional formatting.

For example

My Range A1 and A2 are merged cell with red color and with some value on it and I have to copy only that red color to Range B1 and B2 which have some different values. Is there any provision to satisfy it?

I tried

Range("A1:A2").Copy

Range("B1:B2").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

but this will copy paste and merge the cell B1 and B2

Can't you use .FormatConditions.Delete on the target range once you copy? Not optimal, but it might be the easier way of implementing it to your code — dadler
If I understand your problem correctly, you are trying to copy paste vales while keeping the formatting but removing the Conditional Formatting, right? If this is the case, after you paste your values onto Range("B1:B2"), do Range("B1:B2").FormatConditions.Delete , this should remove the conditional formatting of the range. Or do you wish to copy the cells and preserve the formatting applied by Conditional Formatting without it being conditional? — dadler
I don't want to change/update the values I have on B1 and B2, I just want to get the color I have under A1 & and A2 to B1 & B2 without merging the cells. — user9184557
If you want the color of a cell after conditional formatting has been applied then you need Cells(1,1).DisplayFormat.Interior.Color — dadler

Havard Kleven Havard Kleven · Accepted Answer · 2020-06-30T21:21:51

I would create a function to detect the backcolor (.Interor) if desired cells, and store these values as your “Copied colors”.

Example of a function to get the RGB-value of a desired cell:

Function getRGB(rcell) As String
    Dim colorVal As String

    colorVal = Right("000000" & Hex(rcell.Interior.Color), 6)
    getRGB = Right(colorVal, 2) & Mid(colorVal, 3, 2) & Left(colorVal, 2)
End Function

Copy Colors but not conditional formatting

3 Answers