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?
 
roger_karamCommented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
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.