# Conditional Formatting with multiple AND/OR criteria

1. ## Conditional Formatting with multiple AND/OR criteria

Hi there,
I need some help conditionally formatting a spreadsheet.
I need to highlight specific data within Column J based on data in four other columns.
Basically i need it to say something like:

Column J will be red if \$B:\$B="clean" AND \$C:\$C="floor" AND \$G:\$G is greater than or equal to 5 but less than 10 OR if \$H:\$H="yes"

Is this even possible?

thanks,
camdameron

2. ## Re: Conditional Formatting with multiple AND/OR criteria

After selecting the range you want to apply conditional formatting to (i.e. J1:J500);
=OR(\$H2="Yes", AND( \$B2= "Clean", \$C2 = "floor", \$G2 >=5, \$G2 < 10))

Does that work for you?

3. ## Re: Conditional Formatting with multiple AND/OR criteria

OK, so now I need to expand on this.

i need an IF formula to capture the formula you gave above (thanks!) but expand upon it. see modifications below

So, IF(=OR(\$H2="Yes", AND( \$B2= "Clean" or b2="clean2", , \$C2 = "floor", \$G2 >=5, \$G2 < 10)) then the true function = "alert" and the false action =""
BUT i also need another true function to this where all criteria are the same, except the G column value is greater than 10.

does this make sense??

4. ## Re: Conditional Formatting with multiple AND/OR criteria

You didn't say what you wanted if G2>= 10. Put that where the ??? is.

=IF(OR(\$H2="Yes", AND(OR(\$B2="Clean", \$B2="clean2"),\$C2="floor", \$G2>=5)),IF(\$G2<10, "Alert", ???),"")

Does that work for you?

5. ## Re: Conditional Formatting with multiple AND/OR criteria

yeah. i think i am going to need to write a macro for this.
i keep running across mroe data that needs to be included so a single cell formula might not be the best.

basically what i need is to determine whether a sample is an alert or action based on different criteria.
i have attached an example so you can see that each location's area has different levels associated with action and alert, so i am unsure how to get "action" or "alert" populated in column J

can you just let me know whether i should look into writing a macro or if a formula will suffice? i think the formula will be too complicated and should probably apply to thentire worksheet.

i really appreciate all your help!

cameron

6. ## Re: Conditional Formatting with multiple AND/OR criteria

We should be able to do this with Index and defined names. Why is MR1 in both the 2nd and 3rd table on sheet2?

7. ## Re: Conditional Formatting with multiple AND/OR criteria

the third should be PR1
i have attached the updated...

8. ## Re: Conditional Formatting with multiple AND/OR criteria

Okay, it's a pretty long formula but appears to work. First it checks to see if it's got mold and is either CR or MR, then it's an automatic "Alert".
I set up some defined names
CRAir, MRAir, PRAir, CRSurface, MRSurface, PR Surface (horizontal)
CRArea, MRArea, PRArea (vertical)
If you go to the name box (where the cell id is, like A1), there's a dropdown with the names and clicking on them will take you to them.

From there it was a matter of using INDIRECT, OFFSET and INDEX to pull back the correct response. I also added conditional formatting to make it easier to see. I think this could be simplified but this is where my brain took me today. The formula in J2 of sheet1 is.......

Does this work for you?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1