VBA change font colour of one character.

[Akashwani]

Hi,

Is it possible for VBA to find all characters in red font on a worksheet and change those characters to a different font colour or bold or underscore?

Eg....
MrExcel = MrExcel
MrExcel = MrExcel
MrExcel = MrExcel


Thanks

Ak

 

[VoG]

Hi. Example for single cell

Sub ToUscore()
Dim i As Long
With ActiveCell
    For i = 1 To Len(.Value)
        If .Characters(i, 1).Font.ColorIndex = 3 Then
            .Characters(i, 1).Font.ColorIndex = xlAutomatic
            .Characters(i, 1).Font.Underline = xlUnderlineStyleSingle
        End If
    Next i
End With
End Sub

 

[Akashwani]

Hi Peter,

Thank you very much for your reply, it works great.
I don't want to be cheeky, but how would I get it to loop through every used cell and apply the formatting?

Thanks

Ak

 

[VoG]

Try this

Sub ToUscore()
Dim i As Long, c As Range
For Each c In ActiveSheet.UsedRange
    With c
        For i = 1 To Len(.Value)
            If .Characters(i, 1).Font.ColorIndex = 3 Then
                .Characters(i, 1).Font.ColorIndex = xlAutomatic
                .Characters(i, 1).Font.Underline = xlUnderlineStyleSingle
            End If
        Next i
    End With
Next c
End Sub

 

[Akashwani]

ray:

Thank you so very much Peter, I will be passing your code on and I'm sure it is going to make someone very happy.

Thanks Peter

Ak