Highlight 2nd Occurrence of a Specific Character in Cell

[chingching831]

Hi there,

Is there an excel formula or VBA code that can highlight the 2nd occurrence of a specific character in cell? For example, the letter "B" appears twice in the cell, how can I highlight the second "B" in blue?

BATBML

Thanks,
Ching

 

[HighAndWilder]

Hi there,

Is there an excel formula or VBA code that can highlight the 2nd occurrence of a specific character in cell? For example, the letter "B" appears twice in the cell, how can I highlight the second "B" in blue?

BATBML

Thanks,
Ching

This give you the basics and can be altered depending on where the cells are and how many cells need to be formatted.

Private Sub subHighlightCharacterInCell()
Dim i As Integer
Dim x As Integer
Dim rng As Range

  Set rng = Range("E5")

  For i = 1 To Len(rng.Value)
    If Mid(rng.Value, i, 1) = "B" Then
      x = x + 1
      If x = 2 Then
        With rng.Characters(i, 1).Font
          .Color = vbBlue
        End With
      End If
    End If
  Next i

End Sub

 

[Peter_SSs]

I'm assuming a number of values in a column.

Before:

Code:

Sub Highlight2nd()
  Dim RX As Object, M As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "B"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      Set M = RX.Execute(a(i, 1))
      If M.Count > 1 Then .Cells(i).Characters(Start:=M(1).firstIndex + 1, Length:=1).Font.Color = vbBlue
    Next i
  End With
End Sub

After:

 

[chingching831]

This give you the basics and can be altered depending on where the cells are and how many cells need to be formatted.

Private Sub subHighlightCharacterInCell()
Dim i As Integer
Dim x As Integer
Dim rng As Range

  Set rng = Range("E5")

  For i = 1 To Len(rng.Value)
    If Mid(rng.Value, i, 1) = "B" Then
      x = x + 1
      If x = 2 Then
        With rng.Characters(i, 1).Font
          .Color = vbBlue
        End With
      End If
    End If
  Next i

End Sub

Thanks a lot! May I know how this can be changed if I have a specific rows of data say for example T6:W6?

 

[chingching831]

I'm assuming a number of values in a column.

Before:

View attachment 118941

Code:

Sub Highlight2nd()
  Dim RX As Object, M As Object
  Dim a As Variant
  Dim i As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "B"
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      Set M = RX.Execute(a(i, 1))
      If M.Count > 1 Then .Cells(i).Characters(Start:=M(1).firstIndex + 1, Length:=1).Font.Color = vbBlue
    Next i
  End With
End Sub

After:

View attachment 118942

Thanks a lot! May I know how this can be changed if I have a specific rows of data say for example T6:W6?

 

[Peter_SSs]

May I know how this can be changed if I have a specific rows of data say for example T6:W6?

The best way to deal with a different layout may depend on exactly what that layout is and what you are wanting to do.

Your statement above mentions rows (plural) of data but your example mentions just one (singular) row.
What is the exact circumstance? If there are multiple rows are all the rows together or are there other rows between that should not be processed? If multiple rows, do they all have the same number of columns to process? etc.

 

[Fuji]

Hi there,

Is there an excel formula or VBA code that can highlight the 2nd occurrence of a specific character in cell? For example, the letter "B" appears twice in the cell, how can I highlight the second "B" in blue?

BATBML

I will not use Regular Expressions anymore since it may give you a big trouble in the future.

VBScript deprecation: Timelines and next steps | Windows IT Pro Blog

Learn about the deprecation of Visual Basic Scripting edition.

techcommunity.microsoft.com

If you still want to use it then simply

Sub test()
    Dim r As Range
    Const s$ = "B"
    With CreateObject("VBScript.RegExp")
        .Pattern = "^([^" & s & "]*" & s & "[^" & s & "]*)" & s & ".*$"
        For Each r In [T6:W6]
            If .test(r) Then r.Characters(Len(.Execute(r)(0).submatches(0)) + 1, Len(s)).Font.Color = vbBlue
        Next
    End With
End Sub

 

[Fuji]

Here's a simple one that doesn't use Regula Expression

Sub test()
    Dim r As Range
    For Each r In [T6:W6]
        If r Like "*B*B*" Then
            r.Characters(InStr(InStr(r, "B") + 1, r, "B"), 1).Font.Color = vbBlue
        End If
    Next
End Sub