excel conditional format not showing in format cell

Question

I have a conditional format which is working an filling the back ground with the right color in the cell, but when i go to inpect the cell with format cell and click on fill it shows that there is no Fill any idea why this would be.

is there away that i can count the color that the consitional format has set using VBA.

currently using tyhe code below and it fails at the color index line.

Function CountRed(MyRange As Range)
CountRed = 0
For Each cell In MyRange
ColorIndex = cell.DisplayFormat.Interior.ColorIndex

If ColorIndex = 43 Then
CountRed = CountRed + 1
End If


Next cell
test = 0

End Function

because there is no fill, there is conditional formatting

Dmitry Pavliv
– Dmitry Pavliv

2014-05-08 12:12:33 +00:00
Commented May 8, 2014 at 12:12 — Dmitry Pavliv
– Dmitry Pavliv, Commented May 8, 2014 at 12:12
for Excel 2010+ Range.DisplayFormat Property

Dmitry Pavliv
– Dmitry Pavliv

2014-05-08 12:23:13 +00:00
Commented May 8, 2014 at 12:23 — Dmitry Pavliv
– Dmitry Pavliv, Commented May 8, 2014 at 12:23

user3636426 · Accepted Answer · 2014-05-19 13:40:23Z

1

this code below will go through each of the conditional formatted exterior color and then will color the cell with a RGB color specified,

Dim MyRange As Range
Set MyRange = Range("F2:J17")

Dim rng As Range

For Each rng In MyRange

If rng.DisplayFormat.Interior.ColorIndex = 3 Then

    rng.Interior.Color = RGB(255, 0, 0)

End If

If rng.DisplayFormat.Interior.ColorIndex = 44 Then

    rng.Interior.Color = RGB(255, 192, 0)

End If
If rng.DisplayFormat.Interior.ColorIndex = 43 Then

    rng.Interior.Color = RGB(146, 208, 80)

End If



Next rng

answered May 19, 2014 at 13:40

user3636426

1771 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manuel Allenspach · Accepted Answer · 2014-05-08 13:05:25Z

0

As simoco pointed out in the comments, you can achieve this via Range.DisplayFormat

Do something like

MsgBox Range("A1").DisplayFormat.Interior.ColorIndex

to get the currently displayed background-color of the cell.

answered May 8, 2014 at 13:05

Manuel Allenspach

12.7k15 gold badges59 silver badges78 bronze badges

Collectives™ on Stack Overflow

excel conditional format not showing in format cell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related