Forum Discussion
Whats_a_profile_name
Mar 23, 2023Copper 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? ...
- Mar 23, 2023
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.
NikolinoDE
Mar 23, 2023Gold Contributor
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)
Kirk_Hansen
Mar 23, 2023Copper Contributor
Thank you: I hadn't been aware of this use of Search.