color part of a cell

[Tody03]

In my worksheet I have in each cell different data (text & numbers)
How can I write a macro that will color in red the numbers between the first comma and the second.

122,-25%,Brkfst,NRF

<tbody>
</tbody>

In the above sample I want to paint -25% in red

In some cells the space between the commas is empty

 

[Rick Rothstein]

Assuming your cells contain text and not formulas, the following macro will do what you want for all cells in the current selection...

[table="width: 500"]
[tr]
	[td]Sub ColorBetween2ndAnd3rdComma()
  Dim Cell As Range
  For Each Cell In Selection
    If Len(Cell.Value) Then Cell.Characters(InStr(Cell.Value, ",") + 1, Len(Split(Cell.Value, ",")(1))).Font.Color = vbRed
  Next
End Sub[/td]
[/tr]
[/table]

 

[JLGWhiz]

If the cells are in column A. Similar to Rick's.

Sub t()
Dim sh As Worksheet, c As Range, spl As Variant
Set sh = ActiveSheet
    With sh
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            spl = Split(c.Value, ",")
            c.Characters(InStr(c.Value, spl(1)), Len(spl(1))).Font.Color = vbRed
        Next
    End With
End Sub

 

[Norie]

Try this, it works with A1:A8 so adjust the range to suit.

Dim cl As Range
Dim ch As Object
Dim I As Long
Dim pos As Long
Dim strSecond As String

    For Each cl In Range("A1:A8")

        pos = InStr(cl.Value, ",")

        If pos > 0 Then
            strSecond = Split(cl.Value, ",")(1)

            For I = pos + 1 To pos + Len(strSecond)

                Set ch = cl.Characters(I, 1)
                ch.Font.Color = vbRed

            Next I

        End If
    Next cl