[Solved] Need Help withMultiple text words in conditional formatting for specific text

[jpnht1983]

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?

 

[Yousuf Shovon]

How do I get more than one specific text word to work in the formatting?

Hello JPNHT1983,

Welcome to our ExcelDemy Forum! Thank you for your post. I understand you wish to set a Conditional Formatting rule for given keywords in the range A1:EX1 in Excel. We will give you 2 rules that will format the cells with the given keywords. We will use the SEARCH and OR functions in the Conditional Formatting rules.

Here are the steps to do so:

• Firstly, select the range A1:EX1 >> go to Home >> Conditional Formatting >> New Rule.

• Now, click Use a formula to determine which cells to format option >> enter the below SEARCH formula in the formula box >> Format to select any format color >> OK.

=OR(ISNUMBER(SEARCH("SN1D", A1)), ISNUMBER(SEARCH("SN1N", A1)), ISNUMBER(SEARCH("SN3D", A1)), ISNUMBER(SEARCH("SN3N", A1)))

​

• Again, you can use the below OR function in the formula box:

=OR(A1="SN1D",A1="SN1N",A1="SN3D",A1="SN3N")

​

• Consequently, you obtain your desired output. Here, only the mentioned keywords are selected.

​

Note: Using A1 without relative reference means Excel will adjust the reference for each cell. This lets the formula check each selected cell for specified keywords and highlights cells containing those keywords.

Read the below article to count the total colored cells in that range:

Count Cells by Color with Conditional Formatting in Excel​

I am attaching the Excel workbook for you. Let me know if it works for you. Thank you for reading.

Regards,
Yousuf Shovon