Conditional Formatting using AND/OR Formulas?

Question

Hi

I have some data in Excel and I want to apply various formatting if it meets

certain critiera. Could anyone help me out with the formulas I need?

Where Country = "England", And Income is <£16,000 And Verification= Y and

Mode = F and Reason is LLL OR MMM conditional formatting should be Green.

Where Country = "England", And Income is <£16,000 And Verification= Y and

Mode = F and Reason is Null conditional formatting should be Yellow

Where Country is Not England OR Income is >£16000 OR Verification is not Y OR

Mode is not F AND Reason is Not Null conditional formatting should be Red

I take it there is no way to have more than 3 conditional formats?

Also this will be ever expanding data set so can I just replicate the

conditional formatting over blank rows and then when data is added the

formatting will be automatically applied?

Thank you in anticipation

Emma

Answer 1

Thanks that's great so based on that condition 2 would be:

=AND($A2="England",$B2<16000,$C2="Y",$D2="F",$E2="")  ?

Assuming by "Null" you mean a blank cell then yes, that should do it for condition 2.

I stll can't figure out condition 3, sorry - could you give me any help?

You said condition 3 is:

Where Country is Not England OR Income is >£16000 OR Verification is not Y ORMode is not F AND Reason is Not Null

You could do with putting some brackets around that to make it crystal clear, but at a guess this should do what you want:

=AND(OR($A2<>"England",$B2>=16000,$C2<>"Y",$D2<>"F"),$E2<>"")

Test it thorougly.  This gives TRUE if:

(Country is Not England OR Income is >=£16000 OR Verification is not Y OR Mode is not F)

AND

(Reason is not blank)

Note also I used >= for the currency test.  You should be careful to avoid using < in one condition and > in another condition since niether will return a result if income **=**16000!  So use >= for "greater than or equal to" or <= for "less than or equal to" in one of your two conditions.

Hope that helps.

Cheers

Rich

Answer 2

If A2 contains England, B2 contains 15000, C2 contains Y, D2 contains F and E2 contains LLL or MMM then you can use:

=AND($A2="England",$B2<16000,$C2="Y",$D2="F",OR($E2="LLL",$E2="MMM"))

as the formula for the first of your conditions.

Note the use of $ in the above formula.  This means it will work on any row, just make sure that A2 is the Active cell when you apply the CF, i.e. click on cell A2, then drag across and down to select your data, then A2 will be the Active cell in the selection

• all other cells' CF will be relative to row 2 which is the same row that we used in the CF formula so they will look at their own row (hope that makes sense).

Yes, I think you are limited to 3 conditions in Excel 2003.  Using Format Painter should replicate the CF on additional rows if required (but inserting a row before the last row would do this automatically, I think).

Hope that helps.

Cheers

Rich

Answer 3

Hi Rich,

Thank you. That makes perfect sense! You guessed exactly what I wanted, sorry I wasn't more clear. I will test it thoroughly.

Thanks again,

Emma

Answer 4

Hi

Thanks that's great so based on that condition 2 would be:

=AND($A2="England",$B2<16000,$C2="Y",$D2="F",$E2="")  ?

I stll can't figure out condition 3, sorry - could you give me any help?

Thanks

Emma

Answer 5

Hi,

i assume that:

country in A1

Income  in B1

Verification in C1

Mode in D1

Reason in E1

and formatted cell  A10

(change as yours)

(all in Sheet1 tab)

Right click on Sheet1 tab, and select View Code,

then paste the code below, on the right

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1,B1,C1,D1,E1")) Is Nothing Then

[A10].Interior.ColorIndex = 3

If [A1] = "England" And [B1].Value < 16,000 And [C1] = "Y" And [D1] = "F" And [E1] = "LLL" Then [A10].Interior.ColorIndex = 15

If [A1] = "England" And [B1].Value < 16,000 And [C1] = "Y" And [D1] = "F" And [E1] = "MMM" Then [A10].Interior.ColorIndex = 15

If [A1] = "England" And [B1].Value < 16,000 And [C1] = "Y" And [D1] = "F" And [E1] = "" Then [A10].Interior.ColorIndex = 6

End If

End Sub