Conditional Formatting A Single Cell Based On Actions of Multiple Others?

[msher704]

Hello Everyone,

First I want to apologize for not exactly being the most fluent person with Excel. I've been experimenting and learning a few things but I'm not as adept as I'd imagine most people are on here. I am a school counselor and am looking to make improvements upon a data tracker that my friend helped to develop a few years ago, with his permission of course.

If you would, please allow me to list below how the sheet is laid out:

Empty Slot| Last Name| First Name| Responsive Service | Date | Length of Time | Behavior | Location | Red Flag? | Notes
*The Bolded Columns then repeat 99 additional times.

What I want to be able to do is have the "Empty Slot" Cell - in each row - become red if any of those 100 "Red Flag?" Cells reads "Yes." Subsequentally, I would want to do this for each Row from Row 4 to 165...

I understand how to get that empty slot to become red based on a the very first instance of the "Red Flag" Cell through conditional formatting. However, my question is whether or not I can do this for the remaining 99 "Red Flag" cells across the row without creating an additional 99 conditional format rules?

I ask this because otherwise I believe that I would have to make 161 different cells each complete with 100 different conditional format rules.

Any help would be much appreciated.

[AlanY]

you can use a helper column to add the number of "Yes" for that row, if it's 1 or more then condition format the empty slot, see example attached

[FDibbins]

Hi, welcome to the forum

1st, I would question why you have those columns repeated 99 more times? Have you considered putting each "record" in a new row, and thereby building a searchable data-base?

Having said that, to do what you want - assuming those are the ONLY columns that will contain YES?...
1. highlight the range you want to apply the conditional formatting to (the data in column A)
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter =countif(B2:AAA2,"Yes")
format fill RED