Conditional formatting - based on a single cell lookup in a range

[rfj1001]

I want to change the background colour of a column header (cell C2) to Green only if one of the cells in the column (C3-C12) has the character C in it. Any other characters, or more than one C, etc. should not change the colour.

Can SKS help out as I just can't seem to get a formula that works.

Version is Excel 365

Many thanks

Rob

 

[AliGW]

What is SKS?

Do I understand correctly that you are looking for a cell that contains just "C" and nothing else? If so, your CF rule for C2 will be:

=COUNTIF(C3:C12,"C")>0

 

[Tetra201]

Try using the following conditional formatting rule for green fill:

=COUNTIF(C3:C12,"*C*")=1

 

[billgyrotech]

Maybe try this in cell C2:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">=SUMPRODUCT(--NOT(ISERROR(FIND("C",$C$3:$C$12))))</code>

 

[billgyrotech]

Sorry I didn't see the previous replies from the experts. Please disregard my reply.

 

[AliGW]

Try using the following conditional formatting rule for green fill:

=COUNTIF(C3:C12,"*C*")=1

But that will not work if I have understood this correctly:

Any other characters, or more than one C, etc. should not change the colour.

 

[Tetra201]

But that will not work if I have understood this correctly:

You may be right. Let's wait to hear from rfj1001.

 

[rfj1001]

But that will not work if I have understood this correctly:

Hi AliGW and others,

Thanks for all the replies

=COUNTIF(C3:C12,"*C*")=1 will certainly pick up the character C in the selected range but, as you allude to, doesn’t handle the other part of what I’m trying to achieve ie “Any other characters or more than one C, etc. should not change the colour (of the output cell).”

It’s this second part of the formula I just can’t seem to get right.

SKS = Some Kind Soul <g><g></g></g>

 

[AliGW]

So did you try the other suggestions? Mine was:

=COUNTIF(C3:C12,"C")>0

 

[Tetra201]

Hi AliGW and others,

Thanks for all the replies

=COUNTIF(C3:C12,"*C*")=1 will certainly pick up the character C in the selected range but, as you allude to, doesn’t handle the other part of what I’m trying to achieve ie “Any other characters or more than one C, etc. should not change the colour (of the output cell).”

It’s this second part of the formula I just can’t seem to get right.

SKS = Some Kind Soul <g><g></g></g>

Posting an example with the expected results could help exclude guesswork and get the right formula. As you can see, different people have understood your description quite differently.

To the best of my understanding, my formula from Post #3 does exactly what's been requested: it returns TRUE if only one cell in range C3:C12 has a "C" in it. If none of the cells has a "C" or if multiple cells have "C", the formula returns FALSE.

If your intention is to have the column header highlighted if there is only one "C" in only one cell, then try this:

=AND(COUNTIF(C3:C12,"*C*")=1,COUNTIF(C3:C12,"*C*C*")=0)

If it's something else, then try posting an example.