Macro search for text and highlight row

kmobile9 · Jun 6th 2016

Hi all,

I'm refining the code on a macro I posted a couple of weeks ago, and one of the issues I'm encountering is how to have my macro highlight a row in red for the text phrase "Winery Direct", when the cell contains more than just these two words. Unfortunately, I'm at the mercy of the sales team who sends me the data file, so sometimes they will send the file and the columns containing only text, but at other times they'll send it with text and dates mixed in this column. The purpose for highlighting this row is so that I can capture it and delete it before importing into my financial software since a Winery Direct has already been created and so this line would create a duplicate record. Make sense?

I know that when the cell contains only "Winery Direct", the highlighting works, but when it contains mixed data, it doesn't so I'm certain I need to add something like a string for contains, but I'm not advanced enough in VBA to know how to code for that. Any advice or pointers is most welcome.

Code

'search column "n", winery direct=red
    Sheets(2).Activate
    With Sheets(2)
        vDataFour = .Range("n1:n" & .Range("n65536").End(xlUp).Row)
    End With
    For m = 1 To UBound(vDataFour, 1)
        itype = vDataFour(m, 1)
            If itype = "WINERY DIRECT" Then
            Rows(m).Select
                With Selection.Interior
                .ColorIndex = 3
                .Pattern = xlSolid
            End With
            End If
        Next m

Display More

cytop · Jun 6th 2016

Re: Macro search for text and highlight row

Perhaps you mean if the string 'Winery Direct' is contained in the cell?

Code

If InStr(itype, "WINERY DIRECT") > 0 Then

kmobile9 · Jun 6th 2016

Re: Macro search for text and highlight row

Ah ha! Yes, that is exactly what I was trying to wrap my brain around. This worked perfectly. Thanks for all your help - it's much appreciated.

**Trebor76** · Jun 7th 2016

Re: Macro search for text and highlight row

I see cytop has beaten me to the punch!! Nonetheless here's what I came up with:

Code

Option Explicit
Sub Macro1()
    Dim lngLastRow As Long
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    With Sheets(2)
        lngLastRow = .Cells(Rows.Count, "N").End(xlUp).Row
        For lngMyRow = 1 To lngLastRow
            If InStr(StrConv(.Range("N" & lngMyRow), vbUpperCase), "WINERY DIRECT") > 0 Then
                .Rows(lngMyRow).Interior.ColorIndex = 3
            End If
        Next lngMyRow
    End With
    
    Application.ScreenUpdating = True


End Sub

Display More

I must ask - why do you highlight the rows only then to delete them? Why not just delete them to start with?

Regards,

Robert

cytop · Jun 7th 2016

Re: Macro search for text and highlight row

But, as VBA is case sensitive by default, yours is more 'correct' ... I always use 'Option Compare Text' but forgot to mention it here.

kmobile9 · Jun 7th 2016

Re: Macro search for text and highlight row

The reason for highlighting the rows is so that I can find them to delete them. The spreadsheet that I receive is from my external sales team, so the report is often very long and, frankly, I just don't feel like scanning a report for rows that I need to delete when I can have the macro, which is already running to find fields that I need to edit to correct misspellings, etc. also highlight rows that are going to create duplicate records. The spreadsheet ultimately gets imported into QuickBooks and the wizards at Intuit are aware that QuickBooks has no duplicate record filter for excel imports, nor do they plan on creating one, so this is the work around I've had to develop.

Macro search for text and highlight row

Participate now!

Share