VBA - How to Find Specific Text and Change the Font Color?

[kschn]

I have a workbook with multiple tabs that I would like to have a VBA process find the underscores "_" and change the font color to white. This text exists on multiple tabs.

What is the most efficient VBA code to achieve this?

 

[davesexcel]

You could use conditional formatting.
This example select column J then goto conditional formatting.

Use

=IFERROR(FIND("_",J1)>0,FALSE)

You can format the font color if true.

 

[kschn]

You could use conditional formatting.
This example select column J then goto conditional formatting.

Use

=IFERROR(FIND("_",J1)>0,FALSE)

You can format the font color if true.

I'm not sure if this will achieve the desired result. The "_" found in a cell will have other text in it. I am looking to change the color of just the "_" while the remaining text remains untouched.

 

[davesexcel]

This will loop through the sheets and loop through column D to turn the "_" to white font, edit as required.

Sub UnderScore()
    
    Dim sh          As Worksheet, rng As Range, c As Range, x
    
    For Each sh In Sheets
        With sh
            Set rng = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            For Each c In rng.Cells
                x = InStr(c, "_")
                If x > 0 Then
                    c.Characters(Start:=x, Length:=1).Font.Color = vbWhite
                End If
            Next
        End With
        
    Next sh
    
End Sub

 

[kschn]

This will loop through the sheets and loop through column D to turn the "_" to white font, edit as required.

Sub UnderScore()
   
    Dim sh          As Worksheet, rng As Range, c As Range, x
   
    For Each sh In Sheets
        With sh
            Set rng = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            For Each c In rng.Cells
                x = InStr(c, "_")
                If x > 0 Then
                    c.Characters(Start:=x, Length:=1).Font.Color = vbWhite
                End If
            Next
        End With
       
    Next sh
   
End Sub

This is a great start but how would you adjust if the "_" can fall into multiple columns across various tabs of the workbook?