VBA Tip: Cell Color with Conditional Formatting

If you're trying to retrieve the colors of a cell in a traditional way, you'll only get the cell's default colors, ignoring any conditional formatting.

To retrieve the displayed color (taking into account conditional formatting), simply add DisplayFormat to your code.

Usage Example

In the following table, cells are colored with conditional formatting:

Ignoring Conditional Formatting

The code will copy the background color and color index of cells B2 and B3 to two other cells:

Sub example()
    
    'Background color
    Range("B10").Interior.Color = Range("B2").Interior.Color
    Range("B11").Interior.Color = Range("B3").Interior.Color
    
    'Color index
    Range("B10") = Range("B2").Interior.Color
    Range("B11") = Range("B3").Interior.Color
    
End Sub

You can notice that the color applied by conditional formatting is ignored by this code:

Taking Conditional Formatting into Account

To consider the displayed color (and thus the conditional formatting), add DisplayFormat:

Sub example()
    
    'Background color
    Range("B10").Interior.Color = Range("B2").DisplayFormat.Interior.Color
    Range("B11").Interior.Color = Range("B3").DisplayFormat.Interior.Color
    
    'Color index
    Range("B10") = Range("B2").DisplayFormat.Interior.Color
    Range("B11") = Range("B3").DisplayFormat.Interior.Color
    
End Sub

This time, the displayed color has been successfully retrieved: