Change Color of Character within Result of Formula

[dwhitey1124]

Hi everyone,
I would like to change the color of all underscores ("_") that are in the output of a formula. Right now I have the code for changing the color in constant cells but can't figure it out for cells that have formulas. Any advice would be much appreciated.

Dim rng As Range
    Dim byt As Byte
       For Each rng In Range("A1:r2000").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

 

[JoeMo]

Hi everyone,
I would like to change the color of all underscores ("_") that are in the output of a formula. Right now I have the code for changing the color in constant cells but can't figure it out for cells that have formulas. Any advice would be much appreciated.

Dim rng As Range
    Dim byt As Byte
       For Each rng In Range("A1:r2000").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

Change xlCellTypeConstants to xlCellTypeFormulas.

 

[Rick Rothstein]

Hi everyone,
I would like to change the color of all underscores ("_") that are in the output of a formula.

To the best of my knowledge, you will not be able to do that. As far as I know, all the characters in the output from a formula must be formatted the same. If you get rid of the formulas and let Change event code populate your cells with text constants, then that same code can change the color of part of that text constant. Is that something you would want to pursue? If so, show us the formula you now have in the cells.

 

[JoeMo]

To the best of my knowledge, you will not be able to do that. As far as I know, all the characters in the output from a formula must be formatted the same. If you get rid of the formulas and let Change event code populate your cells with text constants, then that same code can change the color of part of that text constant. Is that something you would want to pursue? If so, show us the formula you now have in the cells.

Thanks for the reminder Rick, I had forgotten that caveat when I posted my reply.