VBA color the max value inside the parentheses in column A

[Stephen_IV]

Good afternoon,

I am having some issues, I need to color the max value inside the parentheses in column A. The code below is what I am working with but cannot get it right. Any help would be appreciated!

Sub xdoit()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Dim r As Range
Set r = Range("A1:A" & lastrow)
For Each xcell In r
    If InStr(xcell.Value, "(") > 0 Then
        parts = Split(xcell.Value, "(")
        For i = 0 To UBound(parts)
            v = Val(Replace(parts(i), ")", ""))
            If v > 0 Then
               xcell.Characters(v, 2).Font.ColorIndex = 3
            End If
        Next i
    End If
Next
End Sub

Book1 A B 1 ABSNT(9), VAB(8) 9 2 VAB(2) 2 3 ABSNT(19), VAB(18) 19 4 VAB(2) 2 5 ABSNT(5) 5 6 ABSNT(47), VAB(7) 47 7 ABSNT(11), VAB(2) 11 8 ABSNT(24) 24 9 VAB(39) 39 10 ABSNT(6) 6 Sheet1

 

[NoSparks]

Give this a try

Sub DoIt()
    Dim lastrow As Long, i As Long
    Dim j As Long, x As Long, HiNum As Long
    Dim r As Range, xcell As Range, parts As Variant

    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    Set r = Range("A1:A" & lastrow)
    
    r.Replace what:=")", replacement:="(("
        
    For Each xcell In r
        parts = Split(xcell.Value, "(")
        For i = 0 To UBound(parts)
            If IsNumeric(parts(i)) And parts(i) > HiNum Then
                HiNum = parts(i)
            End If
        Next i
        
        j = Len(Trim(HiNum))
        xcell.Replace what:="((", replacement:=")"
        x = InStr(xcell.Value, HiNum)
        xcell.Characters(x, j).Font.ColorIndex = 3
        
        HiNum = 0
    Next xcell

End Sub

 

[Stephen_IV]

Ahh!! That does it!! Thank you NoSparks, that helped me a lot!!

 

[NoSparks]

You're welcome, thanks for reporting back.

 

[Rick Rothstein]

Here is another (more compact) macro that you can consider...

Sub DoIt2()
    Dim MaxVal As String, Cell As Range, Parts As Variant
    For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
      Parts = Split(Cell.Value & "(0", "(")
      MaxVal = CStr(Application.Max(Val(Parts(1)), Val(Parts(2))))
      Cell.Characters(InStr(Cell.Value, MaxVal), Len(MaxVal)).Font.Color = vbRed
    Next
End Sub

 

[Stephen_IV]

Thanks Rick, I appreciate your help and guidance!