Test Formatting?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

C Brandt - 16 May 2007 22:43 GMT

Many of my spread sheets use color to indicate status. Some of these
formulas are quite extensive and quite frankly I forget some of the evolved
logic. Is there a way to test for the color of a cell.
For example, I can write an IF statement as follows :
IF(B2>50,"Above Average","Below Average")
I would like to write an IF statement like:
IF(AND(B2 is colored Green, A2 is colored Yellow), "Issue Resolved","Issue
not Resolved")

Any suggestions?

Craig

Reply to this Message

Gord Dibben - 16 May 2007 23:51 GMT

How did you color the cells............manually or via Conditional Formatting?

If manually, you will need a User Defined Function to test the color.

See Chip Pearson's site for the necessary UDF's

Then you would need a formula like such................

=IF(AND(cellcolorindex(B2)=10,(cellcolorindex(A2)=6)),"issue resolved","issue
not resolved")

If colored via CF, just use the original criteria in your IF(AND( formula.

Gord Dibben MS Excel MVP

>Many of my spread sheets use color to indicate status. Some of these
>formulas are quite extensive and quite frankly I forget some of the evolved
[quoted text clipped - 8 lines]
>
>Craig

Reply to this Message

C Brandt - 17 May 2007 03:18 GMT

The cell was colored via CF. The formulas to color the cell are quite
extensive and in realty, this pass, I want action if the cell is not
colored. Normally I would just use the logic that created the color, but if
there was a way to sense the color of the CF. it certainly simplifies the
logic to make other decisions.

Thanks,
Craig

> How did you color the cells............manually or via Conditional Formatting?
>
[quoted text clipped - 23 lines]
> >
> >Craig

Reply to this Message

Gord Dibben - 17 May 2007 03:46 GMT

Chip's site also has UDF's to return the CF colors.

http://www.cpearson.com/excel/CFColors.htm

Guess I could have posted that originally<g>

Gord

>The cell was colored via CF. The formulas to color the cell are quite
>extensive and in realty, this pass, I want action if the cell is not
[quoted text clipped - 40 lines]
>> >
>> >Craig

Reply to this Message

Mike Rogers - 17 May 2007 03:48 GMT

C Brandt

You can identify all or all the same conditional formatting by going
to>Edit>Go to>Special> and select conditional formats. Now you can see all
of them or only the ones that are the same.

Mike Rogers

> The cell was colored via CF. The formulas to color the cell are quite
> extensive and in realty, this pass, I want action if the cell is not
[quoted text clipped - 40 lines]
> > >
> > >Craig

Reply to this Message