color part of a cell

Tody03

New Member
Joined
Jun 21, 2014
Messages
48
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,691
Office Version
  1. 2010
Platform
  1. Windows
Assuming your cells contain text and not formulas, the following macro will do what you want for all cells in the current selection...
Code:
[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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
If the cells are in column A. Similar to Rick's.
Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Try this, it works with A1:A8 so adjust the range to suit.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,820
Messages
5,598,297
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top