Solved

Excel 2010 Conditional Format with multiple conditions

Posted on 2011-02-23
17
1,604 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
  • +1
17 Comments
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
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:Christian de Bellefeuille
ID: 34963353
ok, between would be WEIGHT >= AND WEIGHT <= 89.  Not both with "<=".

Let me check, i'll be back in a minute
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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:Christian de Bellefeuille
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:Christian de Bellefeuille
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:Christian de Bellefeuille
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:Christian de Bellefeuille
ID: 34963844
you are right barry, i'm getting used to that...
0
 
LVL 10

Expert Comment

by:Christian de Bellefeuille
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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

630 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