Problem getting VBA to recognize RGB of conditionally formatted cells - only gets normal..

[marsh_e79]

Hi Everyone,

I'm coming up against a tricky problem, hoping someone can shed a little light for me. I've got conditional formatting on a sheet which highlights cells where data does not meet certain validation criteria (i.e. age is greater than realistic maximum, or one event's date is later than another one which it can't logically be). I'd like to have VBA check the (CF'd) RGB of each cell, and as per below, if there are 192,0,0 dark red cells (errors) to do thing 'A', else if there are only amber (warnings) 255,192,0 then provide option 'B'.

I've been playing around with VBA:

Please Login or Register  to view this content.

The problem is that it seems like the code above ignores the CF cell colours and only pays attention to the underlying (and overridden with CF) cell colour, so this is falling flat at the moment.

Any help would be massively appreciated.

Thanks

[protonLeah]

Cell.interior.color is a different property from cell.formatconditions.interior.color. You should post a small sample workbook.To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

[marsh_e79]

Hi There,

Thanks for your reply, here's a sample. Cheers.

[marsh_e79]

Hi, protonLeah. I've been playing around with your 'cell.formatconditions.interior.color' suggestion, but have found that 'interior' doesn't seem to be a supported property of 'formatconditions' (and none of the supported properties seem relevant to what I'm doing). Anymore thoughts on what to try? Cheers.

[shg]

You'd make life easy on yourself if you would instead test the conditions that control conditional formatting.

I won't say it's impossible to determine the Excel 2007 CF-induced cell formatting via VBA, but have seen several people waste a lot of time trying.

[protonLeah]

Try this one:

Please Login or Register  to view this content.

[Andy Pope]

Please Login or Register  to view this content.

[shg]

Thanks for that, Andy. I guess it took 2007 to make the need apparent.