Forum Discussion
Macros or Find
You can use Conditional Formatting for this purposes.
Let's say you want to look at column D. If a cell contains the word Excel (with possibly other words), the adjacent cell in column E should be highlighted in green.
Select column E (the column you want to format).
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ISNUMBER(FIND("Excel",D1))
(We use D1 because it is the first cell in the selection)
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
If you want to highlight cells in column E if the cell in column D contains both Word and Excel, do the same, but with the formula
=AND(ISNUMBER(FIND("Word",D1)),ISNUMBER(FIND("Excel",D1)))
HansVogelaar Is there a way to have this formula apply to more than one column without duplicating the formula and editing. I tried ending with ,D1:C1 but that did. not work all of the time. Thanks, John
Please explain in more detail what you want to accomplish.
- I was referring to the =ISNUMBER(FIND("WORD",B1)) Formula that you helped me with recently. In stead of searching just the B Column, I would like too search B and C columns for the term "Word". I have done it by creating two formulas and I was wondering if it could be done in one. John
HansVogelaar Thank you so much for responding however I am have difficulty in following your directions which may be because I am using a Mac. When I get to the Conditional Formatting screen it does not show me a place to enter your formula. I can see where it says formula under the minimum and maximum value. That did not work for me. This is a new subscription for me and I am transitioning from an old version. It is almost like starting over.
Thanks. John
jhicks5charternet The screens on a Mac are a bit different.
1) New Rule... on the Home ribbon.
Then choose Classic in the Style field.
Then select "Use a formula .........." from the field below the Style.
Now you can enter your formula.
- I did enter my formula and it worked for one cell only. How do I get it to search the entire column and reformat the appropriate cells? Thanks John
This should work the same on Mac as on Windows.
Make sure that you select Conditional Formatting > New Rule..., and then 'Use a formula to determine which cells to format'.
