Using Find All to fill cells of a particular condition with colors

I would like to find all the companies which have PTE in their names and highlight their names with yellow. There are a total of 99 company names listed from A2 to A100.

1. First, highlight the range A2:A100.

2. Activate the Find function using Ctr + F, or go to “Home” Tab and click on Select and find icon.

3. Type in the word PTE into the Find box.

4. Click the Find All button.

5. The results are displayed in the white below the find function.

6. With the first entry selected, use the mouse cursor to scroll to the end of the results.

7. Press and hold on to the shift. Use the mouse to click on the last result.

8. All the cells containing the word “PTE’ are selected.

9. Close the FIND window.

10. Fill the selected cells with a colour.

Click play to watch the steps in action.

Here is another application of Find All.

ALTernative Solution 1:

1. Select the range A1:A100.

2. Activate the auto filter function by going to the home tab, sort and filter icon, =filter.

3. Click on the inverted triangle located on the right side of A1, select text filters, contains.

4. Type in the word “PTE” and click OK.

5. Select the range that is displayed. Press the function key F5 or Ctrl + G. Click on the Special button in the Goto window. You can also go straight to Goto special using the command in the home tab:

6. Select the option “visible cells only”. Click OK.

7. Fill the selected cells with a colour.

8. When you remove filter, cells containing the word “PTE” are highlighted.

ALTernative Solution 2

1. Select the range A1:A100

2. Go to conditional formatting and click cells containing the word “PTE”. Click Ok.

3. Using conditional format is a little more sticky because the colour take precedent over normal fill cells. This means that if you try to colour the cell containing the “apple’ with another colour, you would be able to do so.

ALTernative Solution 3

1. If you wish to make Method 3 a little more powerful by allowing someone to find words other than apple, you can use the following steps.

2. Enter the word “PTE” in any away from your list, e.g. D2

3. Select the range A1:A100

4. Go to conditional formatting and refer to the cells in D2. Click Ok.

5. The cells containing the word “PTE” are selected. If you change the word in cell D2 to LTD, the cells will change accordingly.

Click here to go back to the top of the find all page.