Solved

Excel 2010 Conditional Format with multiple conditions

Posted on 2011-02-23
17
1,586 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
Comment Utility
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
Comment Utility
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
Comment Utility
ok, between would be WEIGHT >= AND WEIGHT <= 89.  Not both with "<=".

Let me check, i'll be back in a minute
0
 
LVL 5

Accepted Solution

by:
roger_karam earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@tommoran:
By the way, my method apply the formatting to the entire row because you have specified it

conditionally format a row
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:cdebel
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
It does.  See the comment just below it.
0
 
LVL 10

Expert Comment

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

Expert Comment

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

Author Comment

by:tommoran
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Run-time 91 error, not sure why 3 21
IF Statement 3 23
Conditional format Excel Cell 3 14
Dynamic Excel Countdown Graphic 21 15
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

728 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now