Conditional Formatting (IF statement)

**Marmie2** · 06-09-2013, 04:49 PM

I've only recently discovered conditional formatting and have used it successfully with simple formulas. Now I need to do something more complicated.

I have 2 columns and here is what I'm trying to make happen.

If the value in column A is 1 or 2 and the value in column B is > 1.15, I want the cell text in column B to be red.
If the value in column A is 1 or 2 and the value in column B is < .85, I want the cell text in column B to be green.
If the value in column A is 1 or 2 and the value in column B is between .85 and 1.15, I want the cell text in column B to be black.

If the value in column A is 3 and the value in column B is > 1.1, I want the cell text in column B to be red.
If the value in column A is 3 and the value in column B is < .9, I want the cell text in column B to be green.
If the value in column A is between .9 and 1.1, I want the cell text in column B to be black.

I'm driving myself nuts trying to figure it out, so perhaps an expert on this board can assist. Hope so!

Thanks.

**FDibbins** · 06-09-2013, 04:59 PM

See if this will get you started?

=OR(B1>1.15,A1=1,A1=2) format text RED
You need to create a new rule for each condition

Shout if you still have a problem

**Marmie2** · 06-09-2013, 10:43 PM

I tried that formula, but it made the entire column red.

So then I kept inputting the other criteria and when I got to one that makes the text green, the entire column turned green.

Not sure what I'm doing wrong.

**Spitzerpl** · 06-09-2013, 11:25 PM

The attached conditions seem to work. I would point out a couple of things, though...

Your last option overrides your first anytime the value is in column A=1. You can determine which one is applied by the order you place the conditional formatting in, though.

You do no account for what happens when the value is equal to 1.15. Do you want that to turn black or red? if black then the back condition should be "<=" rather then just "<".

one thing to keep in mind with conditional formatting is that you must build your conditional formatting rule based off the Active cell.

conditionalFormatingSamples.xlsx

**FDibbins** · 06-09-2013, 11:39 PM

Perhaps it would help if you provided us with a sample workbook (no sensitive data?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.