Check Conditional Formatting

[Tommy Bak]

Hello
I Know this must have been arswered many times before, but I can't find it.

Is it possible in VBA to check if cell meets the given conditions for formatting.

ex. if a range of cells have been formatted to be interior.colorindex=15 (grey) if they are above 10, is it then possible to check the color in vba??

I've tried to make a makro that would only select the cells in an area that meet the conditions, but I failed somewhere.

Sub Macro2()
Dim Same As Range
Firsttime = True
test = Selection.Interior.PatternColorIndex
Selection.SpecialCells(xlCellTypeSameFormatConditions).Select
For Each c In Selection.Cells
If c.Interior.PatternColorIndex <> test Then
If Firsttime = True Then
Set Same = Range(c.Address)
Firsttime = False
Else: Set Same = Application.Union(Range(c.Address), Same)
End If
End If
Next
Same.Select
End Sub

TIA Tommy

[Tommy Bak]

What is happening here??
No repplies at all.
is this to hard a question?? or are you missing some info?+

regards Tommy

[Tom Urtis]

I'm not sure what you're asking.

Are you wanting to evaluate the color shading of a cell to make sure it is (in your example) gray-shaded if its numeric value is greater than 10? If so, why not start from scratch by simply unshading all the cells in your range, conditionally format that range for the cell color to be shaded gray if it contains a 10 or higher, and that's that.

Alternatively, you could AutoFilter the range to display only cells in that range which contain a number higher than 10, then select the filtered column(s) you want to shade, and click on Format > Cells > Patterns tab to choose the #15 gray index square from the palette. Remove the filter and you're good to go.

Both approaches avoid VBA to keep things simple.

But, are you asking for VBA to evaluate the color index of each cell? And if so, index 15 gray in particular? If so, try this macro...modify it for column of interest (assumes A contains the numbers/shading), and the column for the evaluation return text (code uses column D):

Sub GrayPatrol()
Dim cell As Range
Dim rG As Range
Set rG = Range([A2], [A65536].End(xlUp))
rG.Offset(, 3).Clear
For Each cell In rG
If cell.Interior.ColorIndex = 15 Then
cell.Offset(, 3).Value = "Yes, gray!!"
End If
Next
End Sub

Keep in mind that with conditional formatting, Excel does not regard the formatted cell as containing a certain color index, so this VBA evaluation would not work for conditionally formatted cells, but would work with actually formatted cells, per the first option I suggested above.

Regarding VBA, there have also been UDFs posted on this board to evaluate a color index by entering a formula that could return any color index number, and the macro above could do so also with modifications. You could locate them by using the Search feature on this board.

Hopefully though, this info might get you started.

[Tommy Bak]

Keep in mind that with conditional formatting, Excel does not regard the formatted cell as containing a certain color index, so this VBA evaluation would not work for conditionally formatted cells,

Thank you very much, Tom.
It was a very good answer.
The above quote was exactly what I was looking for. So what I was trying to do was impossible. I just needed to get that confirmed, so i could stop thinking about what I did wrong.

Regards Tommy