Compare on cell to another, and highlight data if found

[bradjedis]

Greetings,

I have a situation where I have Data in Col F (singular data entry ex: 24681) and I need to compare to col G where there is a string of numbers. Ex: 12345, 45654, 66531, 24681

I need to compare and if found, highlight JUST that exact string found in the cell.

VBA is perferred.

Thanks,
Brad

[HansV]

Is this about a single cell in column F or multiple cells?
And is this about a single cell in column G or multiple cells?
Perhaps you could attach a small sample workbook.

[bradjedis]

as requested.

Range is variable.

[bradjedis]

trying again

[HansV]

See the attached version.

Test1.xlsm

[bradjedis]

Sweet. I like the results!

[bradjedis]

Additionally, I have tried to add .Font.Bold = true, to this but am not being successful. How do I get this line to make it Bold?

Range("P" & r).Characters(Start:=p, Length:=n).Font.Color = vbRed & .Font.Bold = True

[HansV]

Like this:

Code: Select all

Sub RemoveColor()
    With Range("G:G").Font
        .ColorIndex = xlColorIndexAutomatic
        .Bold = False
    End With
End Sub

Sub ColorEntries()
    Dim r As Long
    Dim m As Long
    Dim s As String
    Dim n As Long
    Dim p As Long
    Application.ScreenUpdating = False
    RemoveColor
    m = Range("G" & Rows.Count).End(xlUp).Row
    For r = 2 To m
        s = Range("F" & r).Text
        n = Len(s)
        p = InStr(Range("G" & r).Value, s)
        If p > 0 Then
            With Range("G" & r).Characters(Start:=p, Length:=n).Font
                .Color = vbRed
                .Bold = True
            End With
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

[bradjedis]

As always, I learn something!!

Thanks,
Brad