Conditional Format vs Interior ColorIndex (03)

Topic

New Reply

I am looking for the reason why the conditional format (interior color: red) is different from a cell’s interior.colorindex of 3.

How does one read the conditional cell color format with VBA?

Thanks,
John

Reply | Quote

Replies

They shouldn’t be different, unless you have changed the default color palette – check this in the Color tab of Tools | Options…

The color index for the first condition in conditional formatting is Activecell.FormatConditions(1).Interior.ColorIndex

Reply | Quote

Hans,

I have not changed the color palette. I have attached a sample file.

If I run code for the active cell ie ActiveCell.Interior.ColorIndex

I receive a -4142.

Regards,
John

Reply | Quote

ActiveCell.Interior.ColorIndex returns the color index specified in the Pattern tab of Format | Cells… It does NOT take conditional formatting into account. The value -4142 is xlColorIndexNone, i.e. transparent.

The reds in the two groups of cells in your attachment are *exactly* the same on my PC. Are they different on yours?

Reply | Quote

Hans,

They are the same on my PC. I understand what you’re saying about the transparency. How does one determine the conditional formatting of red? Each condition can be a different color.

Regards,
John

Reply | Quote

For example:

Sub WhatColor()
Dim i As Integer
If ActiveCell.FormatConditions.Count > 0 Then
For i = 1 To ActiveCell.FormatConditions.Count
MsgBox “Condition ” & i & ” – color index ” & ActiveCell.FormatConditions(i).Interior.ColorIndex
Next i
Else
MsgBox “No conditional formatting”
End If
End Sub

Reply | Quote

Hans,

I now understand how the conditional format retains the colorindex for each of the cells in question. What appears to be a challenge for me is how Excel handles the visual display of the conditional format. In my sample file the conditional format was to fill the cell with ‘red’ if the amount in the cell is zero. I want to test the fill color, in this case I belive the code is ActiveCell.Interior.ColorIndex = 3. Unfortuantely it displays ‘red’ but the results are -4142 or transparent.

Is it possible to test for a ‘red fill’ or am I missing the concept.

Regards,
John

Reply | Quote

I repeat from higher up in this thread[indent]

---

ActiveCell.Interior.ColorIndex returns the color index specified in the Pattern tab of Format | Cells… It does NOT take conditional formatting into account.

---

[/indent]If you want to know which color is currently displayed, you can either use the same tests as conditional formatting in your code, or use the functions from Chip Pearson’s Conditional Formatting Colors.

Reply | Quote