Conditional Format Cell Based on 'No Match' in Another Range(row).

**Orestees** · 02-21-2013, 12:54 AM

Formatting Demo (OFF).xlsx
The attached is an example of what the end result should look like but I'm not sure what kind of conditional formatting function would accomplish this.

Essentially, Trainee 1 has a day off during the week, so there's no alert in the last column of that row (I).
Trainee 2 has no days off during the week, so there should be an alert in column (I) for that row, 'Not OFF.'

I've seen plenty of methods for dealing with similar functions between columns, but nothing exact and nothing for rows.

In the final version of this form, if the word 'OFF' is typed into the bottom cell of the row, it's also placed in the top. This means that the formula doesn't need a preference of where to look to find value.

Let me know if you have any questions with this or if there's just no way this will function.

Thanks in advance for the help!

**Kevin@Radstock** · 02-21-2013, 02:58 PM

Hi Orestees

May be using the IF & LOOKUP, see the attached file. You need to stay clear of merging cells, they are the work of the devil!

**Orestees** · 02-21-2013, 08:16 PM

Thanks for taking a look Kevin! I tried the formula in my working version but for some reason the LOOKUP wasn't working. Due to your help though, I reworked the formula to =IF(OR(B3="OFF", C3="OFF", D3="OFF", E3="OFF", F3="OFF", G3="OFF"),"OFF","NOT OFF") and that seemed to fix it. It's not as pretty but this one worked.

The only thing I can think that caused an issue is that the cells containing "OFF" are actually formulas themselves. I forgot to add this into the demo, my bad.

I've reattached the demo with the formula just in case someone else finds a use for it. It also has the formulas that create the "OFF" designator along with formatting. Formatting Demo (OFF).xlsx

Thanks again Kevin!

**Excel Fox** · 02-21-2013, 09:03 PM

This would be much simpler

=IF(COUNTIF(B3:G3,"OFF"),"OFF","NOT OFF")

**Orestees** · 02-22-2013, 02:24 AM

Yes it is! That looks much cleaner than the prior one. Kevin's was very much like it but I think due to the way the equations worked, it couldn't coincide. Thanks to the both of you for the help!! Greatly appreciated!