Solved

Excel 2010 Conditional Format with multiple conditions

Posted on 2011-02-23
17
1,591 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:tommoran
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 10

Expert Comment

by:cdebel
ID: 34963249
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
 
LVL 1

Author Comment

by:tommoran
ID: 34963343
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
 
LVL 10

Expert Comment

by:cdebel
ID: 34963353
ok, between would be WEIGHT >= AND WEIGHT <= 89.  Not both with "<=".

Let me check, i'll be back in a minute
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 5

Accepted Solution

by:
roger_karam earned 500 total points
ID: 34963433
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34963453
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
 
LVL 10

Expert Comment

by:cdebel
ID: 34963461
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
 
LVL 5

Expert Comment

by:roger_karam
ID: 34963465
sorry, typo on the <=89...

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

Expert Comment

by:cdebel
ID: 34963494
@tommoran:
By the way, my method apply the formatting to the entire row because you have specified it

conditionally format a row
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34963524
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34963567
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34963622
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
 
LVL 1

Author Closing Comment

by:tommoran
ID: 34963788
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 34963828
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
 
LVL 1

Author Comment

by:tommoran
ID: 34963841
It does.  See the comment just below it.
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34963844
you are right barry, i'm getting used to that...
0
 
LVL 10

Expert Comment

by:cdebel
ID: 34963865
Tommoran:  Barry did posted that comment bellow roger, not roger himself
0
 
LVL 1

Author Comment

by:tommoran
ID: 34963880
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question