|I made a "New Rule" for each term I wanted to highlight. I have about 20 terms|
You do not need 20 Rules,
there is a way, using a Defined Name, to use only one Rule.
But it limits you to only One Color also.
If you just want to Highlight the cells, and you don't really mind using only one color then try this:
First you will need to Define a Name:
Somewhere out of the way on your spread sheet, like column XX enter your 20 target words so you have a list like:
Now, Highlight your list of words.
Next, on the ribbon, select the Formula Tab
Click on the Define Names button
In the Name box enter a descriptive name, like MyColors
Leave the Scope on Workbook
In the Refers To: box at the bottom, be sure it is referencing your list of words.
It should look like: =Sheet1!$XX$1:$XX$20
Now we will need to define the Conditional Formatting:
1) Select your cell or range of cells: IE: A1:Z100
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:
6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK
All the cells with your various target words should change to the pretty color you selected.
The advantage to this, is that you can now change or add words to your list
without having to define a new Rule in CF.
Make sure you choose a color that you have not used in your other CF Rules, or you can Delete the other Rules before you apply the above.