Excel 2010 Conditional Format with multiple conditions

I have a spreadsheet that I would like to conditionally format a row based on several criteria.  I'm going to do my best to provide an example to help relay what I need.

Let's say I have 3 columns
DIVISION, AGE, WEIGHT

I want to highlight the rows with the following sets of criteria

Yellow Highlight if...
DIVISION="Division 1"
AGE & WEIGHT (here is where I get lost...)
If (AGE=8 AND WEIGHT =>90) OR (AGE=9 AND WEIGHT =>75)
THEN Highlight the row Yellow

Okay not too bad, now I totally lost myself in this next request...
Red Highlight if...
DIVISION="Division 2"
AGE & WEIGHT
IF (AGE=8 AND WEIGHT <=85 AND WEIGHT <=89) OR (AGE=9 AND WEIGHT <=70 AND WEIGHT <=74)
THEN Highlight row Red

Hope this makes sense.
Thank you in advance,
Tom
LVL 1
tommoranAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
roger_karamConnect With a Mentor Commented:
IF (AGE=8 AND WEIGHT <=85 AND WEIGHT <=89) OR (AGE=9 AND WEIGHT <=70 AND WEIGHT <=74)

= OR (AND(Age=8,Weight>=85,Weight>=89),AND(age=9,weight>=70,weight<=74)

where age and weight should be replaced with the corresponding cells

-RK
0
 
Christian de BellefeuilleProgrammerCommented:
Hummm that line doesn't make sense:

IF (AGE=8 AND WEIGHT <=85 AND WEIGHT <=89) OR (AGE=9 AND WEIGHT <=70 AND WEIGHT <=74)

If it's <= 89, then why testing with 85?
0
 
tommoranAuthor Commented:
I didn't know how to accomplish a between the weights of 85 and 89.  And I did mistake that symbol.  It was supposed to read greater than or equal to 85 and less than or equal to 89.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Christian de BellefeuilleProgrammerCommented:
ok, between would be WEIGHT >= AND WEIGHT <= 89.  Not both with "<=".

Let me check, i'll be back in a minute
0
 
barry houdiniCommented:
Hello Tom,

You can use a combination of OR and AND functions in conditional formatting so if Divison is column A and Age column B and Weight column C then you can select the whole range (I'm assuming it starts at row 2, so e.g. A2:C100) and use this formula for Yellow

=AND($A2="Division 1",OR($B2=8,$C2>=90),OR($B2=9,$C2>=75))

and then for Red

=AND($A2="Division 2",OR(AND($B2=8,$C2>=85,$C2<=89),AND($B2=9,$C2>=70,$C2<=74)))

Note the $ signs, you need those

regards, barry
0
 
Christian de BellefeuilleProgrammerCommented:
If you add this code to your sheet, it will check for the modifications and apply the formatting

 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Age As Integer
    Dim Weight As Integer
    Dim Division As Integer
    
    For Each Cel In Target.Rows.EntireRow
        Division = Cel.Cells(1, 1)
        Age = Cel.Cells(1, 2)
        Weight = Cel.Cells(1, 3)
        
        If Division = 1 Then
            If (Age = 8 And Weight >= 90) Or (Age = 9 And Weight >= 75) Then
                Cel.Cells(1, 1).EntireRow.Interior.Color = vbYellow
            Else
                Cel.Cells(1, 1).EntireRow.Interior.Color = xlNone
            End If
        ElseIf Division = 2 Then
            If (Age = 8 And Weight >= 85 And Weight <= 90) Or (Age = 9 And Weight >= 70 And Weight <= 74) Then
                Cel.Cells(1, 1).EntireRow.Interior.Color = vbRed
            Else
                Cel.Cells(1, 1).EntireRow.Interior.Color = xlNone
            End If
        End If  
    Next
End Sub

Open in new window

0
 
roger_karamCommented:
sorry, typo on the <=89...

= OR (AND(Age=8,Weight>=85,Weight<=89),AND(age=9,weight>=70,weight<=74))
0
 
Christian de BellefeuilleProgrammerCommented:
@tommoran:
By the way, my method apply the formatting to the entire row because you have specified it

conditionally format a row
0
 
Christian de BellefeuilleProgrammerCommented:
In case you had difficulties to implement it in your workbook, here's how it's done

Alt-F11 to open the VB Editor
Double-click on ThisWorkbook in the tree on the left
Then copy the code i gave you in the white area on your right

(See the attached files depending for Excel 2010 and 2003)
ConditionalFormatting.xlsm
ConditionalFormatting2003.xls
0
 
barry houdiniCommented:
I made a mistake for the red condition - should be this

=AND($A2="Division 1",OR(AND($B2=8,$C2>=90),AND($B2=9,$C2>=75)))

see attached in action with some randomly generated data - press F9 to generate nes data and see if the formatting changes

regards, barry
26842525.xlsx
0
 
barry houdiniCommented:
mmm......I just re-read the question (that always helps!) and I see that I got yellow and red the wrong way round. You probably get the idea, anyway but here it is with the colours swapped

regards, barry
26842525v2.xlsx
0
 
tommoranAuthor Commented:
This was the easiest solution by far.  Even though cdebel had a great solution it was more complex than I needed however it may be very well the solution for someone else.

Great job roger_karam

Thank you everyone for your input...
0
 
barry houdiniCommented:
Hello Tom,

Surely roger's suggestion doesn't actually do what you asked - it doesn't take the Division into account, or am I missing something?

regards, barry
0
 
tommoranAuthor Commented:
It does.  See the comment just below it.
0
 
Christian de BellefeuilleProgrammerCommented:
you are right barry, i'm getting used to that...
0
 
Christian de BellefeuilleProgrammerCommented:
Tommoran:  Barry did posted that comment bellow roger, not roger himself
0
 
tommoranAuthor Commented:
Not just below it because that would be yours.  I didn't have a problem figuring out the rest after I got the proper statement.  My apologies for not being clear on that.  I rushed into fixing my problem and got so excited when it worked.  I will rework and post my entire solution as soon as I get it completed.  Please forgive me EE community as I was in hind site a bit hasty to award points without a true explanation.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.