Solved

# Conditional formatting based on multiple criteria

Posted on 2014-09-18
157 Views
I would like to implement conditional formatting in my Excel sheet as follows:

A2 cell background turns green if the following 2 criteria are met:
B2 is blank
C2 = "DOG 1" or "DOG 2" or "DOG 3" or "DOG 4" or "DOG 5"

Is is possible to do this with the Conditional Formatting feature, or is VBA required? I have attached a simple spreadsheet as an example of what I am looking for.

Thanks,
Andrea
0
Question by:Andreamary
• 2

LVL 27

Accepted Solution

Glenn Ray earned 300 total points
ID: 40330763
No attachment! :-)

However, it should be possible to check for this condition by creating the following rule in Conditional Formatting:
=AND(\$B2="",OR(\$C2="DOG 1", \$C2="DOG 2",\$C2="DOG 3",\$C2="DOG 4",\$C2="DOG 5"))

-Glenn
0

LVL 24

Assisted Solution

Phillip Burton earned 200 total points
ID: 40330766
Yes -
Select A2.
Go to Home - Conditional Formatting - New Rule
select "Use a formula to determine which cells to format"
Type the following in Format values where this formula is true:

=and(b2="",or(c2="DOG 1",C2="DOG 2",C2="DOG 3",C2="DOG 4",C2="DOG 5"))

Click Format... and select your formatting. Click OK.
Then click OK.
0

LVL 27

Expert Comment

ID: 40330769
Appending...sample file attached.

-Glenn
EE-Q-28521167.xlsx
0

Author Closing Comment

ID: 40330883
Thanks for providing solutions so quickly...much appreciated! Sorry about forgetting the attachment...! Given that both responses came in within a couple of minutes of each other, I've shared the points. I identified the first solution as the best one, since it was first and it included the "\$" in the formula which was beneficial.

Cheers,
Andrea
0

## Featured Post

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…