VBA to change text colour

[DeekMan]

Good afternoon,

I have been trying to get code to change the colour of some text in a cell, but to leave part black.

I attached a snip from my spreadsheet, and the code that I am running. What I am trying to achieve is "NAME" in black, "(00:00)" in Red, as per the first row, which I have done manually. The (00:00) will always have 1 space after the last letter. And to be fair, I have copied this code.

Any help would be appreciated.

Sub ColourSomeText()

Dim P As Integer
Dim l As Integer
Dim wb As Workbook
Dim ws1 As Worksheet
Dim LastRow As Long
Dim St1 As String

Set wb = ThisWorkbook
Set ws1 = wb.Sheets("EMR")

LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LastRow

P = InStr(Cells(i, 5).Value, " ")
l = Len(Cells(i, 5).Value)
    
    ws1.Cells(i, 5).Characters(Start:=P + 1, Length:=8).Font.ColorIndex = 3
Next

End Sub

 

[davesexcel]

You can probably search for the "("

Sub colotText()
    Dim ws As Worksheet, rng As Range, c As Range, x
    Set ws = ActiveSheet
    With ws
        Set rng = .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)

        For Each c In rng.Cells

            x = InStr(c, "(")
            c.Characters(Start:=1, Length:=x - 1).Font.Color = vbRed
            c.Characters(Start:=x, Length:=7).Font.Color = vbBlack
        Next
    End With

End Sub

 

[DanteAmor]

It's a shame you only gave an example. So the constant is a space " " or the parenthesis "("?
However use the following, adjust the " " or "(" to whatever you need:

Sub ColourSomeText()
  Dim p As Integer, n As Long, c As Range
  For Each c In Sheets("EMR").Range("E2", Sheets("EMR").Range("E" & Rows.Count).End(xlUp))
    p = InStr(c.Value, " ")   '<--- Change to "(" if you prefer
    If p > 0 Then c.Characters(p, Len(c.Value)).Font.ColorIndex = 3
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[DeekMan]

It's a shame you only gave an example. So the constant is a space " " or the parenthesis "("?
However use the following, adjust the " " or "(" to whatever you need:

Sub ColourSomeText()
  Dim p As Integer, n As Long, c As Range
  For Each c In Sheets("EMR").Range("E2", Sheets("EMR").Range("E" & Rows.Count).End(xlUp))
    p = InStr(c.Value, " ")   '<--- Change to "(" if you prefer
    If p > 0 Then c.Characters(p, Len(c.Value)).Font.ColorIndex = 3
  Next
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Hi Dante,

The code worked perfectly, but only with the "(" change.

Thank you.

 

[DeekMan]

You can probably search for the "("

Sub colotText()
    Dim ws As Worksheet, rng As Range, c As Range, x
    Set ws = ActiveSheet
    With ws
        Set rng = .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)

        For Each c In rng.Cells

            x = InStr(c, "(")
            c.Characters(Start:=1, Length:=x - 1).Font.Color = vbRed
            c.Characters(Start:=x, Length:=7).Font.Color = vbBlack
        Next
    End With

End Sub

Hi Dave,

Unfortunately, this worked in reverse...

But many thanks for your answer.

DW