Reference text color in conditionall formatted cells

Question

What we are trying to accomplish is to reference text color in conditionally formatted cells that meet a certain metric. We have a for loop that checks the text color of each cell going down a row.

Colored cells done via conditional formatting can't be referenced via font.color as manually/VBA colored cells are (as far as I know), so we are looking for a way to reference the conditionally formatted color/look in the formatting function and grab the color.

Dim rstarpos As Long
Dim cstartpos1 As Long

rstartpos = 9
cstartpos1 = 3

For i = rstartpos To 10
Sheets("Scorecard").Select
Cells(i, cstartpos1).Select
MsgBox Cells(i, cstartpos1).Font.Color

Font.color would ideally report what the font color of the cell in the loop is. However, because of the conditional formatting it's not. Is there a way to call the conditionally formatted cell's color?

Thanks for your help.

Brian Camire Brian Camire · Accepted Answer · 2011-07-19T20:32:28

If you're using Excel 2010, you can use the DisplayFormat property of a range to get its displayed format, including the effects of conditional formatting (and table styles). In your example, the last line would change to:

MsgBox Cells(i, cstartpos1).DisplayFormat.Font.Color

Hope this helps.

Reference text color in conditionall formatted cells

1 Answers