Forum Discussion

Whats_a_profile_name's avatar
Whats_a_profile_name
Copper Contributor
Mar 23, 2023
Solved

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

    • Kirk_Hansen's avatar
      Kirk_Hansen
      Copper Contributor
      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.
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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)

    • Kirk_Hansen's avatar
      Kirk_Hansen
      Copper Contributor
      Thank you: I hadn't been aware of this use of Search.

Resources