This function is possibly something that should be built into Excel as standard, but even as Excel 2016 has just been released it’s not there.
There are times when doing a CTRL+F to find specific words or a string of text in your worksheet will suffice, but there may be times when you want to find all of them and easily identify them when skimming through your data. This is especially useful if you are using Excel as a database and want to find specific words.
The example below will consider a list of words on the ‘Words’ tab and identify where those specific words appear on your activesheet. Using the last row function for the words allows you to add and remove words from your list as you please, and makes sure each word is scanned. The range we have set for this example is the first 2 words in the ‘Words’ tab being identified in the first 255 rows and the first 255 columns of your activesheet. Upon being identified in a cell, the cell will be coloured red.
Feel free to adapt the code as required, or contact us through the contact form or leave a message and we’ll see what we can do to help.
For n = 1 To 2
For r = 1 To 255
For c = 1 To 255
tst = Worksheets("Words").Range("A" & n)
d = ActiveSheet.Cells(r, c)
If InStr(1, d, tst) > 0 And d <> "" Then
ActiveSheet.Cells(r, c).Interior.Color = vbRed