CODEWITHSUNDEEP
excelvba
user3086751
user3086751

Reputation: 205

excel conditional format not showing in format cell

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

Upvotes: 1

Views: 406

Answers (2)

user3636426
user3636426

Reputation: 177

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

Upvotes: 1

Manuel Allenspach
Manuel Allenspach

Reputation: 12725

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.

Upvotes: 0

Related Questions