Forum Discussion
Need help for conditional formatting with more than one specific text word
I need help to write a conditional formatting code for specific text.
I have a worksheet with countif. I built a conditional format for specific text to color the cell a certain color, and the countif function totals the colors at the end of the worksheet.
How do I get more than one specific text word to work in the formatting? I had to build multiple conditional formatting rules for different words that turn into the same color.
Example, one keyword is SN1D, one keyword is SN1N, one keyword is SN3D, one keyword is SN3N. All of these keywords turn the cell the same color. I had to build 4 different conditional formatting rules to make this work because I don't know how to make it work.
How can all 4 of these keywords be grouped together in one conditional formatting rule that turns the cells to the same color for a range of cells from A1 to EX1?
Select A1:EX1.
A1 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=OR(A1="SN1D",A1="SN1N",A1="SN3D",A1="SN3N")
Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.- Does that work for a range? I do not need it for one cell. I need it for a range of cells from A1 to EX1.
Excel automatically adjusts the cell reference. For B1, it looks at the value of B1, etc.
This is because the formula uses the relative cell reference A1.
If we had used the absolute cell reference $A$1, the rule would color all cells in A1:EX1 depending on the value of A1 alone.
1. Define 'Keywords' as a named item:
2. Conditional Formatting formula will then be:
=XOR(A$1=Keywords)- How does that work for a range of cells from A1 to EX1?
Nothing changes. Be sure to select the range you want the conditional formatting to apply to and use the correct referencing styles:
