Conditional Format assistance?

[Destrachan]

Condition formatting is getting the best of me at the moment. I need
to set up a Conditional Format that has 3 rules.

1. if the number in the cell is >= 85% then the background of the cell
needs to be green
2. if the number in the cell is < 85%, then the background of the cell
needs to be yellow
3. if the formula in the cell returns an error, then no change to the
original formatting of the cell.

As it stands right now, I’m using a formula that will return a dash
(-) if there’s an error. Rule 1 and 2 I can get to work, it’s just
that I can’t get Rule 3 for the life of me. I’ve looked at
http://www.contextures.com/xlCondFormat03.html but that hasn’t helped
me at all.

Thanks for the help,
Destrachan

 

[Destrachan]

Can nobody help me with this?

 

[Peter T]

If I follow, you don't want the CF to format a cell with a dash

Seems quite straightforward
for the first CF
Cell value Is : between : 0.85 and : 1234567
Cell value Is : less than or equal to : 0.85

Amend that large number to suit.

Regards,
Peter T


Condition formatting is getting the best of me at the moment. I need
to set up a Conditional Format that has 3 rules.

1. if the number in the cell is >= 85% then the background of the cell
needs to be green
2. if the number in the cell is < 85%, then the background of the cell
needs to be yellow
3. if the formula in the cell returns an error, then no change to the
original formatting of the cell.

As it stands right now, I’m using a formula that will return a dash
(-) if there’s an error. Rule 1 and 2 I can get to work, it’s just
that I can’t get Rule 3 for the life of me. I’ve looked at
http://www.contextures.com/xlCondFormat03.html but that hasn’t helped
me at all.

Thanks for the help,
Destrachan

 

[Rick Rothstein \(MVP - VB\)]

I used this formula for Condition 1...

Condition 1 Formula: =OR(A1="-",A1="")

and did not set any formatting for it. I'm not sure what formulas you are
using for your two current conditions; but, for my testing, I used the
following for the other two conditions...

Condition 2 Formula: =A1<85%

Condition 3 Formula: =A1<85%

For these two conditions, set your colors accordingly.

Rick

 

[Destrachan]

I used this formula for Condition 1...

Condition 1 Formula:   =OR(A1="-",A1="")

and did not set any formatting for it. I'm not sure what formulas you are
using for your two current conditions; but, for my testing, I used the
following for the other two conditions...

Condition 2 Formula:   =A1<85%

Condition 3 Formula:   =A1<85%

For these two conditions, set your colors accordingly.

Rick






- Show quoted text -

Brilliant!!! Rick, thank you very much, that's exactly what I needed.