how to highlight the datas using VBA

Question

I have this code that looks for a match using the value that an user entered via input box. I want the found data to be highlighted but my code isn't doing that.

Dim holdstr As String
Dim fset As Range

holdstr = UCase(InputBox("Enter name"))

For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

    If holdstr = Sheet1.Cells(i, 1).Value Then

        MsgBox "Record found!", vbInformation, "Message"

        txtFirst.Text = Sheet1.Cells(i, 1).Value
        txtLast.Text = Sheet1.Cells(i, 2).Value
        txtMid.Text = Sheet1.Cells(i, 3).Value

        With Selection.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With

    End If
Next i

score 1 · Answer 1 · edited May 23 '17 at 12:16

I have changed your comparison method so instead of using the = operator you are now using a more reliable function called StrComp()

Removed unnecessary variables.

Changed selection to Columns A:C of the matched row. Try to avoid using .Select or .Selection

Once a match if found the cells in column A,B,C will be filled with Yellow color

Sub HighlightDates()

    Dim holdstr As String
    holdstr = UCase(InputBox("Enter name"))

    For i = 2 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
        If StrComp(holdstr, Sheet1.Cells(i, 1).Value, vbTextCompare) = 0 Then
            MsgBox "Record found!", vbInformation, "Message"

            With Range("A" & i & ":C" & i).Interior
                .ColorIndex = 6
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
            End With
        End If
    Next i
End Sub

@user2123999 please consider accepting this as an answer if its helpful to you :) there is a green check mark top left corner of the answer — , Sep 11 '13 at 11:31

score 0 · Answer 2 · answered Sep 11 '13 at 16:15

0

As an alternate, it's a bit more code but I prefer Range.Find loops

Sub tgr()

    Dim rngColor As Range
    Dim rngFound As Range
    Dim strName As String
    Dim strFirst As String

    strName = InputBox("Enter Name", "Highlight Name")
    If Len(strName) = 0 Then Exit Sub   'Pressed cancel

    With Sheet1.Range("A2", Sheet1.Cells(Rows.Count, "A").End(xlUp))
        If .Row < 2 Then Exit Sub   'No data
        .Resize(, 3).Interior.Color = xlNone   'Remove any prior highlighting
        Set rngFound = .Find(strName, .Cells(.Cells.Count), xlValues, xlPart)
        If Not rngFound Is Nothing Then
            strFirst = rngFound.Address
            Set rngColor = rngFound.Resize(, 3)
            Do
                Set rngColor = Union(rngColor, rngFound.Resize(, 3))
                Set rngFound = .Find(strName, rngFound, xlValues, xlPart)
            Loop While rngFound.Address <> strFirst
            rngColor.Interior.ColorIndex = 6
            MsgBox rngColor.Cells.Count / 3 & " records found!", vbInformation, "Search Completed"
        Else
            MsgBox "No matches found", , "No Matches"
        End If
    End With

End Sub

answered Sep 11 '13 at 16:15

tigeravatar

26,199
5
30
38

@tigeravatar..It also worked, very much appreciated..Follow-up question if you dont mind, how will I transfer the matched data from excel into my textboxes?..(txtID for ID number, txtFirst for first name, and txtLast for last name)..I'm learning VBA as a beginner. – user2123999 Sep 12 '13 at 07:57
I don't know which column is which, so I'm guessing col A is ID Number, col B is First Name, and col C is Last Name. In which case, just under the `Do` use `txtID.Text = rngFound.Text` for ID. For first name: `txtFirst.Text = rngFound.Offset(, 1).Text`. For last name: `txtLast.Text = rngFound.Offset(, 2).Text` – tigeravatar Sep 12 '13 at 14:44
@tigeravatar..Sorry I forgot to mention which column they belong..Good thing you guessed it right. This is so helpful. Thanks!, I learned so much from those information you have shared :D . – user2123999 Sep 13 '13 at 06:47
@tigeravatar..Hi, been analyzing your code given above and it really worked well.although there are some lines that I find hard to understand as a beginner.Lately I been trying to record time/stamp time and date( time:Col.C & Date: Col.A) in sheet2 that has the same cell from the matched data in sheet1, but none of it worked so it brought me here again. – user2123999 Sep 15 '13 at 09:07

how to highlight the datas using VBA

2 Answers2