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
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Conditional Format vs Interior ColorIndex (03)
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?
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
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
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.
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.
Notifications