VBA Partial Match Text Conditional Formatting

[dwhitey1124]

Hi everyone, I am working on a file that requires constant use of underscores. I would like to hide these underscores. I having been trying to find a way to select all instances of underscores (in unhidden and hidden sheets) and change the font to transparent. The closest I got is the below, but it's not good. Any help would be very much appreciated. Sub Underscore()
Dim rng As Selection
Dim Cell As Range
Dim start_str As Integer
Workbook.Select
Set rng = Selection
For Each Cell In rng
start_str = InStr(Cell.Value, "_")
If start_str Then
With Cell.Characters(start_str, 1).Font
.ColorIndex = xlNone
End With
End If
Next
End Sub

 

[ParamRay]

.
.

Try something like this:

Sub ConcealUnderscores()

    Dim wks As Worksheet
    Dim rng As Range
    Dim byt As Byte
    
    For Each wks In ActiveWorkbook.Worksheets
        For Each rng In wks.Cells.SpecialCells( _
            xlCellTypeConstants, _
            xlTextValues)
            
            byt = InStr(rng, "_")
            If byt > 0 Then
                rng.Characters(byt, 1).Font.Color = _
                    rng.Interior.Color
            
            End If
        Next rng
    Next wks

End Sub

 

[dwhitey1124]

Thanks gpeacok. There are 2 additional things I would like in the code: 1) I would like it to format more than just 1 underscore in a cell. For example, abc_def_ghi is only formatting the first underscore. I would also like the second to be formatted. 2) I would like this macro to run constantly and automatically. Thanks in advance.

.
.

Try something like this:

Sub ConcealUnderscores()

    Dim wks As Worksheet
    Dim rng As Range
    Dim byt As Byte
    
    For Each wks In ActiveWorkbook.Worksheets
        For Each rng In wks.Cells.SpecialCells( _
            xlCellTypeConstants, _
            xlTextValues)
            
            byt = InStr(rng, "_")
            If byt > 0 Then
                rng.Characters(byt, 1).Font.Color = _
                    rng.Interior.Color
            
            End If
        Next rng
    Next wks

End Sub

 

[dwhitey1124]

Sorry for the double post. I would like to add that I already am using "Private Sub Worksheet_Change(ByVal Target As Range)" on the main sheet. Is there a way of having 2 worksheet change event macros on the same sheet with different key ranges? This would be one way of having the macro run automatically. Thanks.

Thanks gpeacok. There are 2 additional things I would like in the code: 1) I would like it to format more than just 1 underscore in a cell. For example, abc_def_ghi is only formatting the first underscore. I would also like the second to be formatted. 2) I would like this macro to run constantly and automatically. Thanks in advance.

 

[ParamRay]

Sorry for the double post. I would like to add that I already am using "Private Sub Worksheet_Change(ByVal Target As Range)" on the main sheet. Is there a way of having 2 worksheet change event macros on the same sheet with different key ranges? This would be one way of having the macro run automatically. Thanks.

You would need to place this code within your existing Worksheet_Change procedure:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wks As Worksheet
    Dim rng As Range
    Dim byt As Byte
    
    For Each wks In ThisWorkbook.Worksheets
        For Each rng In wks.Cells.SpecialCells( _
            xlCellTypeConstants, _
            xlTextValues)
            
            For byt = 1 To Len(rng.Value)
                If Mid$(rng.Value, byt, 1) = Chr(95) Then
                    rng.Characters(byt, 1).Font.Color = _
                        rng.Interior.Color
                End If
            Next byt
        Next rng
    Next wks

End Sub

 

[ParamRay]

.
.

Actually, it would be better to use a Workbook_SheetChange event-handler for the above code, since we're dealing within underscores on all worksheets...

 

[dwhitey1124]

If this is now a workbook change event, would I put the code on This Workbook instead of the sheet that has the worksheet event currently?

.
.

Actually, it would be better to use a Workbook_SheetChange event-handler for the above code, since we're dealing within underscores on all worksheets...

 

[ParamRay]

If this is now a workbook change event, would I put the code on This Workbook instead of the sheet that has the worksheet event currently?

Yes, exactly.

And you'll need to change the first line to:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 

[dwhitey1124]

How would you modify the code to also look in the results of formulas, not just xltextvalues?

Yes, exactly.

And you'll need to change the first line to:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)