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: