Code to determine color resulting from conditional format.

w_dart

Board Regular
Joined
Dec 20, 2006
Messages
57
Hi All,

I've got the following code:

Sub ratings()
Dim sh As Worksheet
Dim cell As Range

Application.ScreenUpdating = False


For Each sh In ActiveWorkbook.Sheets
sh.Activate

For Each cell In Range("a1:m46")
If cell.Interior.ColorIndex = 35 Then
cell.Select
'rest of the code is irrelevant
End If
Next

Next sh

Application.ScreenUpdating = True

End Sub


This one checks whether the cell color is green, if that's true the cells contents are evaluated in a rating system. That works great.
The problem is that the cell color is often determined via conditional formatting and the above code snipet ignores this. My question is how to adapt the above code to recognize cell colors wich are colored by conditional formatting.

Any ideas?

Thanks for your help,

Kind regards

WD
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

w_dart

Board Regular
Joined
Dec 20, 2006
Messages
57
Thanks XLD,

Thanks for the quick response. As you say it's not trivial !

And it looks like this won't work for my Conditional Formatting formula
=IF(WEEKDAY($B5)<>1;TRUE;FALSE)

as it keeps giving me the #VALUE! error. ( I copied
the CFColorindex and the CFArrayColours into a module and tested it
directly on the worksheet. )

Any ideas?

Thanks for your help!
 

Forum statistics

Threads
1,181,374
Messages
5,929,577
Members
436,680
Latest member
kellyjkon

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
Top