Partial change font colour within cell - VBA

[jimsjams]

A friend wanted help to write a macro to change the text colour (to turn blue text to black and red text to blue) I thought I could help and so made this:

Sub changecol()

For Each Cell In Selection
If Cell.Font.ColorIndex = 37 Then
Cell.Font.ColorIndex = 1
End If
Next

For Each Cell In Selection
If Cell.Font.ColorIndex = 3 Then
Cell.Font.ColorIndex = 37
End If
Next

End Sub

I then found out that his cells had multiple font colours within in them (blue, black and red) and only part of the cell text should be changed.

This is much more complicated. I was wondering whether there is a way to use the cell.characters.font property to count the starting character number and length of say the blue text in order to convert it to black.

Would this even be possible?

Any thoughts greatly appreciated!

Thanks,

James

 

[dor1angray]

What if I need to have everything that is in square brackets highlighted as well as all HTML tags? I have tried modifying the above but was only able to get the brackets turned to red whereas the rest of the text was left in black:

Sub Format_Characters_In_Found_Cell()
Dim Found As Range, x As String, FoundFirst As Range


x = "["
x = "]"
On Error Resume Next
Set Found = Cells.Find(what:=x, LookIn:=xlValues, LookAt:=xlPart)


If Not Found Is Nothing Then
    Set FoundFirst = Found
    Do
      'Format "x"
        With Found.Characters(Start:=InStr(Found.Text, x), Length:=Len(y))
            .Font.ColorIndex = 3
            .Font.Bold = False
        End With
        Set Found = Cells.FindNext(Found)
    Loop Until FoundFirst.Address = Found.Address
Else
    MsgBox x & " could not be found.", , " "
End If


End Sub

Any ideas?

Thanks!

 

[dor1angray]

I guess I need to add RegEx here, something like
^\[.*?\] for the square brackets for instance. Though, I am not sure how to embed this expression.

Please help.