SOLVED

# Conditional formatting: cell fill depends on character and case

Copper Contributor

# Conditional formatting: cell fill depends on character and case

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

# Re: Conditional formatting: cell fill depends on character and case

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

• 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.

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.

NikolinoDE

I know I don't know anything (Socrates)

best response confirmed by NikolinoDE (Gold Contributor)
Solution

# Re: Conditional formatting: cell fill depends on character and case

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.

# Re: Conditional formatting: cell fill depends on character and case

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.

# Re: Conditional formatting: cell fill depends on character and case

Thank you: I hadn't been aware of this use of Search.