Forum Discussion
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?
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.
4 Replies
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.
- 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.
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.
I know I don't know anything (Socrates)
- Thank you: I hadn't been aware of this use of Search.
