Excel - Conditional formatting for two values

Kawule

New Member
I'm trying to find out why my 2nd conditional formatting does not work.

Basically on my sheet there is a column where the pH must be greater than 6.5 and less than 8.5 (Looks like 6.5 > pH < 8.5)

The conditional formatting is applied to the next column over for the sake of simplicity lets say this is column B which has its own IF statement to print out OK if true, Bad if False

So my first one I wrote it like this:
=AND(A5>=6.5,A5<=8.5) and this will set the cell colour to green if I put in say 7 for pH, this is applied to Column B starting @ B5

The second one I wrote it like this:
=AND(A5<6.5,A5>8.5) and its supposed to set the colour to red, this also applies to Column B starting @ B5 however, I think there is something wrong with the logic on this one which is maybe why it doesn't work.

Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

BQardi

Active Member
Yeah, a value can't be less than 6.5 AND at the same time be greater than 8.5!
Code:
``[COLOR=#333333]=[/COLOR][COLOR=#ff0000]OR[/COLOR][COLOR=#333333](A5<6.5,A5>8.5)[/COLOR]``

Kawule

New Member
I'll give that a shot and also would this logic work? =AND(A5<>6.5,A5<>8.5) if I used that I think it might work but I have a feeling that would cause some issues

EDIT: Your suggestion worked thanks! Logic in programming always confuses me lol

Last edited:

BQardi

Active Member
=AND(A5<>6.5,A5<>8.5) would ALWAYS return TRUE!
Translated it would sound something like this:
different from 6.5 AND different from 8.5
To return FALSE the value in A5 would need to be both 6.5 AND 8.5 which is impossible!

Replies
5
Views
392
Replies
3
Views
167
Replies
7
Views
185
Replies
8
Views
518
Replies
9
Views
250

1,191,690
Messages
5,988,105
Members
440,126
Latest member
duque00

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?

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

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