SOLVED

Conditional formatting: cell fill depends on character and case

Copper Contributor

I want to set a cell's fill color according to its content's character and case. Something like:

a   Red

A   Orange

b   Yellow

B   Green

c    Blue

C    Purple

What's a simple way to do this?

 

4 Replies

@Whats_a_profile_name 

One simple way to set conditional formatting based on text and case in Excel is to use a formula with the SEARCH function. 

You can follow these steps:

  • Select the cells you want to format.
  • Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  • Enter a formula like =SEARCH(“a”,A1)>0 for lower case “a” and =SEARCH(“A”,A1)>0 for upper case “A”.
  • Choose the fill color you want for each rule and click OK.

You can repeat these steps for other letters and colors as well. 

 

…or Format cells by using color scales

Color scales can help you understand data distribution and variation, such as investment returns over time. Cells are shaded with gradations of two or three colors that correspond to minimum, midpoint, and maximum thresholds.

 

Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to.

 

On the Home tab, click Conditional Formatting.

 

Conditional Formatting

Point to Color Scales, and then click the color scale format that you want.

 

The top color represents larger values, the center color, if any, represents middle values, and the bottom color represents smaller values.

 

Hope I could help you with these information / links.

 

NikolinoDE

I know I don't know anything (Socrates)

best response confirmed by NikolinoDE (Gold Contributor)
Solution

@Whats_a_profile_name 

The function EXACT will also give a case sensitive match, so the conditional formatting could be based upon the formulas 

 

= EXACT(B2,"a")
= EXACT(B2,"A")
= EXACT(B2,"b")
= EXACT(B2,"B")
= EXACT(B2,"c")
= EXACT(B2,"C")

 

which return TRUE or FALSE.

image.png

PeterBartholomew_0-1679571332320.png

 

 

 

(Virus scan in progress ...)
I'm a stranger here so I don't know how to upvote or accept-as-answer or whatever it is one does in this forum. But your answer was clear, simple to use, and thoroughly demonstrated, so Thank You.
Thank you: I hadn't been aware of this use of Search.
1 best response

Accepted Solutions
best response confirmed by NikolinoDE (Gold Contributor)
Solution

@Whats_a_profile_name 

The function EXACT will also give a case sensitive match, so the conditional formatting could be based upon the formulas 

 

= EXACT(B2,"a")
= EXACT(B2,"A")
= EXACT(B2,"b")
= EXACT(B2,"B")
= EXACT(B2,"c")
= EXACT(B2,"C")

 

which return TRUE or FALSE.

image.png

PeterBartholomew_0-1679571332320.png

 

 

 

View solution in original post

(Virus scan in progress ...)