Conditional Format to look in multiple cells based on another cell

**proaction** · 12-13-2011, 05:32 PM

Using Excel 2010. We have a spreadsheet to give people a quick view of errors/missing data. We download a report and then paste it in this spreadsheet daily.

I'm using conditional formatting. I've got it down for some of the criteria we are looking for (e.g. if E2 = "1390" & F2 = RR then change color). But now I've been asked to go further with this...

Here is what I need to do: If colum E is "1390" and columns A-D do NOT have one of these codes, "496" "428.0" "416.0" then highlight A-D.

I've attached an example. I don't care about what color it turns. I can change that later.

I'll need to do this for a couple other codes in column E, but if I can get some guidance on one, I should be able to get the others down.

Thanks,
Carolyn

**JBeaucaire** · 12-13-2011, 07:29 PM

Highlight A2:D100, then the CF setting would be:

FormulaIs: =AND($E2=1390, AND(ISERROR(MATCH(428, $A2:$D2, 0)), ISERROR(MATCH(496, $A2:$D2, 0)), ISERROR(MATCH(416, $A2:$D2, 0))))
Pattern: choose a color

**proaction** · 12-27-2011, 03:22 PM

This worked beautifully. Sorry for responding back so slowly. I haven't been able to try it until now.

With this figured out, they now would like a range of numbers to be highlighted. So if column E is "E5070" and columns A-D are not equal to or between 600-815, turn a color.

Thanks,
Carolyn

**JBeaucaire** · 12-28-2011, 11:00 PM

Want to attach a sample sheet demonstrating what you mean... sample data and sample results manually colored?

**proaction** · 12-29-2011, 11:02 AM

Attached is the example. It also has the conditional formatting you gave me for my initial response and notes for what I'd like it to do. I highlighed in yellow what should be highlighted based on my "criteria" as well as added a column with x's for what should be colored.

I've changed the range to be the following 480.0- 508.9.

What I'm trying to get it to do: If column E is "E0570" and columns A-D do not have ANY of the numbers within the range of 480.0 - 508.9, highlight A-D.

I may be over doing it with the examples/explaining what I want, but... It doesn't matter how many DX codes there are (1,2,3, or 4). If 1 of them is within the range, it shouldn't change color.

Scenarios below have HCPC (column E) of E5070:
1. If there are 2 DX numbers and 1 of them is 481 & the other is 999, A-D would NOT be highlighted.
2. If there are 3 DX numbers and none of them are equal to or betweeen 480.0-508.9, highlight
3. If there is 1 DX number and it is not equal to or between 480.0-508.9, highlight
4. If there is 1 DX number which is 500.5, it will NOT be highlighted.

Hope this helps.
Thanks,
Carolyn