Forum Discussion
How to conditional format any cell in an excel file based on the list of words I have.
I have an excel data that have columns A to AQ and rows 1 to 2022. Means that my data range is A1 to AQ2022. On the other side i have 50 words list, i want an formula to search for these 50 words in A1 to A2022 and highlight any cell that contains the partial text.
For example: the 50 words list contains a word "rabigh" then
Excel should search for rabigh in A1 to AQ2022 and highlight any cell that contains the partial word "rabigh" or "petro rabigh" or "rabigh power" etc.
3 Replies
Select data A1:AQ2022 >> Go to Conditional Formatting >> Manage Rules >> New Rule >> Format only cell that contains
use the same method multiple times. If you want VBA, kindly DM
The attached file shows an example for range A1:J36 where each cells that contains a word from range L1:L19 is highlighted in orange. The ranges and the rule for conditional formatting can be adjusted as required.
As variant, rule formula could be
=SUM( COUNTIF( A1, "*" & $L$1:$L$19 & "*") )
