Macro Not Working for Search in Multiple Columns

**vcs1161** · 03-07-2013, 11:35 AM

I have this macro that seems to have a quirk where it is not looking through all the columns that are selected. This is looking for keywords and changing the font to bold red type for just those keywords in a cell. It works but am now seeing that it is not looking through all the columns selected in the range. Any thoughts of what you see could be the problem is greatly appreciated.

Please Login or Register  to view this content.

**tigeravatar** · 03-07-2013, 02:26 PM

vcs1161,

Here is an overhauled version of your code, with comments. Give it a try:

Please Login or Register  to view this content.

**vcs1161** · 03-07-2013, 02:39 PM

I am getting this error message on the last line "Erase arrTerms"

Error 1004 "Unable to get the Text Property of the Range Class"

**tigeravatar** · 03-07-2013, 02:46 PM

Did you alter the code? arrTerms() should be dimmed as String, not Range

**tigeravatar** · 03-07-2013, 02:49 PM

Alternately, if you could upload a sample workbook that is experiencing the error, I can take a look at it, because I have been unable to duplicate that error.

**vcs1161** · 03-07-2013, 03:42 PM

I did not alter the code but it seems to do this and hang when I select a range of columns. But it does work when I select a range of specific cells. There will be times when selecting the column will be more efficient since there can be some very large worksheets. The other thing I noticed is that sometimes it will not find the part of a string after the second, third, etc. times. For instance, I ran this again after my first successful run was complete and ran another search for "wed" but it did not find that in Wednesday (column A). And yet it was found after I reset the font and ran it in the first pass/attempt.

I attached a file that I am testing this on.

TestingMacroSearch.xlsx