SOLVED

# Conditional formatting if multiple cells have an 'x' in them

Occasional Contributor

# Conditional formatting if multiple cells have an 'x' in them

I do a lot of manual scheduling. I want to alert myself that I've scheduled somebody twice in one week. 2 X's turns the student's name red so I know I messed up (e.g. John Smith cell turns red). Is there a formula I can use for this?

 Student MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY John Smith x x
7 Replies

# Re: Conditional formatting if multiple cells have an 'x' in them

@shrimpusa Use Conditional Formatting with the formula =COUNTA(B2:F2)>1

# Re: Conditional formatting if multiple cells have an 'x' in them

Thanks!@Doug_Robbins_Word_MVP I had to change the >1 ti >2, but this was just what I needed.

# Re: Conditional formatting if multiple cells have an 'x' in them

That would not result in the cell being shaded Red until a student was scheduled on 3 days whereas your original requirement was to detect if a student had been scheduled twice.

# Re: Conditional formatting if multiple cells have an 'x' in them

You're right, I just discovered that, thanks.

# Re: Conditional formatting if multiple cells have an 'x' in them

There's one more wrinkle. I have additional info in other cells that seems to be a problem with the COUNTA. I've set up a conditional formula so that anytime there's an X in D, F, H or J, the name turns green. But I want to set up an additional formula for those same cells so that if I do 2 X's (schedule somebody for 2 days by accident) the name changes red.

best response confirmed by Sergei Baklan (MVP)
Solution

# Re: Conditional formatting if multiple cells have an 'x' in them

@shrimpusa Why not use

=COUNTIF(D3:J3,"x")>1

as the CF rule?

Worked, thanks!