Excel 2003 conditional formating, need to highlight any cell that CONTAINS any of 21 words, eg. egg or carrot?

Question

I have a spreadsheet that contains text.

I want to seach only one particular column and any time Excel finds any one of 21 words that I have identified as important to me, Excel will change the format of that cell, say to a background color of red.

So for example, if one of the words I am looking for is the string "cat" and the cell contains "he can catch the ball" that cell would be formatted, because the string "cat" is contained in amongst the text in that cell.

Again, I want it to search for any of 21 words that I have identified. I expect the answer is going to be under Format -> Conditional Formatting, and I am pretty sure has to use the OR variable somehow because of the "3 condition" limitation. But so far I have not been able to figure it out after much searching and experimenting.

Alternatively, if it has to invoke VBA macros, I'll try that too.

I tried one word at a time using Conditional Formating but it erases the previous conditional format. So if I search for "cat", it highlights all the instances of Cat. But when I subsequently search for "dog", it ERASES all the instances where cat was highlighted and then just highlights DOG. VERY frustrating.

Thanks in advance for your help.

Answer 1

Start by selecting the first cell in the column that will contain data that you want to conditionally format. For my example I will assume you are using cell C3. If not, then change the three references accordingly.

Go to Format > Conditional Formatting

Select Cell Value Is > Equal to

then paste this formula into the box to the right of Equal to:



=IF(ISERROR(IF(LEN(C3)>0, FIND(","&LOWER(C3)&",", ",cat,dog,owl,"), #VALUE!)),"=",C3)



(Go ahead and paste both lines at once - it will work)



Then click Format > Patterns > Red (or whatever color you want) then click OK



Now, whenever one of the words in the list appears in cell C3 it will turn red. Of course, you will probably want to edit the list.



Deconstructing this formula will reveal how it works:



LOWER(C3) converts the text in C3 to lower case so that the find is case insensitive (CAT will work the same as Cat or cat). So, all the words in this string should be lower case. Concatenating both leading and trailing commas forces the find to work only with complete words in the list, so dog will match but do won't.



The FIND funcction looks for the text in C3 (with leading and trailing commas concatenated) within the given string (",cat,dog,owl,"). Note that I used commas, you can use whatever delimiter you want (except double quotes).



The inner IF function checks the length of whatever is in C3 to make sure that at least something is there, and if so, calls the FIND function. If not, it returns the #VALUE! error. (This keeps blank cells from turning red)



Wrap that whole mess in an ISERROR function to determine if you have a match (no errors). If there is a match then the outer IF function returns the value in C3. If there is not a match it returns just an equals sign (not something you'll likely ever find in a cell all by itself).



The conditional format checks to see if the whole function returns the value in C3. If so then the condition is called for (the cell turns red).





Now, to use the condition further down in the column just right-click cell C3 and choose Copy. Select the rest of the cells you want to apply the condition to, right-click in one of them and choose Paste Special > Formats > OK.



Since we used relative references to cell C3 in our conditional formatting formula they will adjust to whatever the current cell is when pasted.



There you have it. Works like a charm for me.



Hope that helps...

Answer 2

Reverse Phone Number Look Up Services

Answer 3

For the best answers, search on this site https://smarturl.im/aD4BM



1. Select the Range (of cells) you want to apply conditional-formatting for example C12:G1011 Make sure that the active-cell is in C12 2. Condition 1 >> Formula Is: =ISNUMBER(SEARCH("Specific Word", C12)) >> Format : Pattern = Red, Font = White note: "Specific Word" can be a reference to a cell. Reasult of Formula in conditional formatting should be a TRUE / FALSE

Answer 4

This Site Might Help You.



RE:

Excel 2003 conditional formating, need to highlight any cell that CONTAINS any of 21 words, eg. egg or carrot?

I have a spreadsheet that contains text.



I want to seach only one particular column and any time Excel finds any one of 21 words that I have identified as important to me, Excel will change the format of that cell, say to a background color of red.



So for example, if one of the words I am...

Answer 5

reverse phone number search compiles hundreds of millions of phone book records to help locate the owner's name, location, time zone, email and other public information.



Use a reverse phone lookup to:

Get the identity of an unknown caller.

Identify an area code.

Recall the name of a person whose number you wrote down.

Identify an unfamiliar phone number that shows up on your bill.

https://tr.im/WAmvq

Answer 6

Alternatively, you could use a macro. In this approach, you select an unused column, say X, and enter your list of words in X1:X21 and hide column X.



If your specific column is not column A, then change all "A" references to your column letter, i.e. "C".



If your list of words is not in column X, change the "X" reference to your list column reference.



Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Range("A1:A" & LastRow).Interior.ColorIndex = xlNone

Application.ScreenUpdating = False

For i = 1 To LastRow

For j = 1 To 21

If InStr(Cells(i, "A"), Cells(j, "X")) Then

Cells(i, "A").Interior.ColorIndex = 37

End If

Next j

Next i

Application.ScreenUpdating = True

End Sub



Select the appropriate sheet and right click the sheet tab. Select 'View code' and paste the macro into the VBE. Close the VBE.