Conditional formatting - highlight entire row based on keywords in different columns

[outofpractice]

I apologize if this has been answered - this site is loading like junk right now and I'm having a hard time navigating it (my company is stuck on IE 8 and I have problems with a lot of sites. Don't know if it is the browser or what the deal it, just know it sucks) Anyhow, this is what I have been trying to do.I have a large excel spreadsheet that has survey answers on it - I want to find any answers that contain specific words in it, such as: paying, billing, payment, etc. These words could be alone in a cell or within other text - i.e. "I am trying to make my monthly payment". These words could be in any column from B - J. I am looking for the easiest way to search for these words and highlight the entire row so I can then filter the entire workbook by that specific row color, but I can only get as far as highlighting the specific cells these words are found in - which means: if it is in column H and I filter by that color from there, I am filtering out the found responses that may be in Column B, C, etc.I hope what I am trying to accomplish makes sense and as always thank you for any assistance!

 

[Brianwarnock]

I don't know how you are doing your search currently, but I would do the entire operation in one macro such as below.

Brian

Sub srch()

'sheet 2 contains a list of words being searched for in cols B to K of sheet1
' if a hit is found then the entire row is highlighted red

Dim lastrow As Long
Dim srchstr As String
Dim r As Long
Dim myRange As Range

With Sheets("sheet1").UsedRange
 lastrow = .Cells(1, 1).Row + .Rows.Count - 1
End With
Set myRange = Worksheets("Sheet1").Range(Cells(1, "B"), Cells(lastrow, "K"))

r = 1
srchstr = Sheets("sheet2").Cells(r, "A")
    
For Each c In myRange
    Do Until srchstr = ""                   'the Do Loop cycles through the search items
        If InStr(c, srchstr) <> 0 Then
            c.EntireRow.Interior.ColorIndex = 3
            GoTo getnextc
         Else
            r = r + 1
            srchstr = Sheets("sheet2").Cells(r, "A")
        End If
    Loop
        
getnextc:
    r = 1
    srchstr = Sheets("sheet2").Cells(r, "A")
Next c
        
End Sub

 

[outofpractice]

AWESOME! Thank you so much. I'm not a coder but I've been able to put this to use and it is doing exactly what I wanted!

 

[Brianwarnock]

Glad you could make the changes required for it to work for you. especially as I hadn't documented it much.

You might like to consider 1 more change, add the If statement shown below to improve efficiency, obviously you will need to change the colorindex value to the one you are using.
The most efficient would be to skip to the next row after a match but that would require me to iterate through the range myself, so unless it becomes an issue I use the For.. Next..

For Each c In myRange
    If c.Interior.ColorIndex = 3 Then GoTo getnextc 'if Row already highlighted skip tests
    Do Until srchstr = ""                   'the Do Loop cycles through the search items

Brian

 

[jfisom55]

Your VBA solution is exactly what I'm looking for. I have been trying to expand the range of columns from B:K out to B:BJ, but I get a type mismatch error. I'm sure it is something simple I'm overlooking, but not being a coder, I'm at a loss.

 

[Brianwarnock]

As it is a while since I did this I no longer have my test sheet, but the change should simply to change the "K" to "BJ" in the Set myrange statement.

What is causing your error I cannot even guess at, which statement is this occurring on ?

You may need to attach your sheet for me to attempt a debug.

Brian

 

[Rx_]

That was excellent code provided to you, I use variations of it in my Access to generate reports in Excel rather than the Access Report object.

Recently, I had some large, multiple spreadsheets generated by code where not the entire row was used. The conditional format can select a cell or range of cells based on conditions in other cells.

The amazing part is how fast this works related to the other method.
So, just in case someone wants to consider a slightly different method, the code is at:
http://www.access-programmers.co.uk/forums/showthread.php?t=257496