Conditional formatting one cell based on a condition in a range.

[bigsi1984]

Hello,

I have a cell S9 that I want to turn red if an of the cells below it contain the word invalid.

All of the ells below it use conditional formatting how either ok or invalid. The issue is there are 200 cells and unless any of the first 10 or so are invalid you have to scroll. I am looking for a solution that immediately highlights if there are any invalid entries.

Can you help?

 

[Trevor G]

If I read this right you could try using a CountIF formula in S9 to tell you how many cells below contain the word Invalid that way you know how many you have to check. You can then also add Conditional Formatting to S9 so if it counts greater than 0 it will change colour.

 

[Legacy 436357]

In cell S9 use this formula under conditional formatting:

=(S10:S250)="invalid"

 

[bigsi1984]

In cell S9 use this formula under conditional formatting:

=(S10:S250)="invalid"

This only changes S9 if S10 says invalid

 

[Legacy 436357]

That is what you asked I believe.

 

[bigsi1984]

But I want it to change S9 if any cells between S10 and S200say invalid. That formula isn’t working for any cell other than S10. Sorry if Iwasn’t clear.

 

[bigsi1984]

This works a treat but is not quite as neat as I would like. If I cannot get the solution I want then I will go with this. Thanks

 

[Legacy 436357]

Sorry I am working on it

 

[Legacy 436357]

In Cell S9:

=IF(SUMPRODUCT(--(S10:S250="invalid")),"Invalid","Valid")

Then in conditional formatting:

=S9="Invalid"

Choose fill color

 

[bigsi1984]

That's great thanks