Check Conditional Formatting

Tommy Bak

Active Member
Joined
Feb 25, 2002
Messages
288
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
What is happening here??
No repplies at all.
is this to hard a question?? or are you missing some info?+

regards Tommy
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top