Make font colour RED of the right side numbers which are… separated by vertical bar

[motilulla]

Using Excel 2010

Hello,

I have got numbers separated by vertical bar in the range cells B6:B25 originally they are font black.

I want is there any way to colour right side numbers which are separated by vertical bar with RED font. Like the image attached.

Excel Question ExcelFourms.xls
	A	B	C
1
2
3
4
5
6		1 | 0
7		9 | 10
8		11 | 0
9		94 | 20
10		108 | 0
11		120 | 140
12		136 | 40
13		919 | 1
14		720 | 10
15		970 | 120
16		1029 | 1
17		1082 | 18
18		1143 | 82
19		1199 | 100
20		1262 | 102
21		1319 | 1212
22		1384 | 1789
23		10015 | 17
24		17225 | 175
25		19230 | 1759
26
27
Sheet3

Thank you.

Regards,
Moti

 

[kevin9999]

One option...

Sub RedFont()
    Dim rng As Range, c As Range, i As Long, n As Long
    Set rng = Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    For Each c In rng
        n = InStr(1, c, "|") + 1
        For i = n To Len(c)
            c.Characters(i, Len(c)).Font.Color = vbRed
        Next i
    Next c
End Sub

 

[kevin9999]

If you wanted the characters bold as well...

Sub RedFontBold()
    Dim rng As Range, c As Range, i As Long, n As Long
    Set rng = Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    For Each c In rng
        n = InStr(1, c, "|") + 1
        For i = n To Len(c)
            With c.Characters(i, Len(c))
                .Font.Bold = True
                .Font.Color = vbRed
            End With
        Next i
    Next c
End Sub

 

[Peter_SSs]

No need for the i loop as the characters are coloured all at once in the first pass of that loop.

Sub RedFont_v2()
  Dim c As Range

  For Each c In Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    c.Characters(InStr(1, c, "|") + 1, Len(c)).Font.Color = vbRed
  Next c
End Sub

 

[motilulla]

One option...

Sub RedFont()
    Dim rng As Range, c As Range, i As Long, n As Long
    Set rng = Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    For Each c In rng
        n = InStr(1, c, "|") + 1
        For i = n To Len(c)
            c.Characters(i, Len(c)).Font.Color = vbRed
        Next i
    Next c
End Sub

If you wanted the characters bold as well...

Sub RedFontBold()
    Dim rng As Range, c As Range, i As Long, n As Long
    Set rng = Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    For Each c In rng
        n = InStr(1, c, "|") + 1
        For i = n To Len(c)
            With c.Characters(i, Len(c))
                .Font.Bold = True
                .Font.Color = vbRed
            End With
        Next i
    Next c
End Sub

kevin9999, absolutely fine results spot on either the macro for font normal or bold.​

Thank you so much for your quick reply and time you spent for solving my question.

Good day and good luck!

Kind Regards,
Moti

 

[kevin9999]

kevin9999, absolutely fine results spot on either the macro for font normal or bold.

Thank you so much for your quick reply and time you spent for solving my question.

Good day and good luck!

Kind Regards,
Moti

Happy to help Moti, but please note Peter's solution in post #4. Better option by far

 

[motilulla]

No need for the i loop as the characters are coloured all at once in the first pass of that loop.

Sub RedFont_v2()
  Dim c As Range

  For Each c In Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    c.Characters(InStr(1, c, "|") + 1, Len(c)).Font.Color = vbRed
  Next c
End Sub

Peter_SSs, as kevin9999, said your macro is shorter and without i loop I will go with it.

I Just added 1 line to make font bold.

Sub RedFont_v2()
  Dim c As Range
  For Each c In Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    c.Characters(InStr(1, c, "|") + 1, Len(c)).Font.Color = vbRed
    c.Characters(InStr(1, c, "|") + 1, Len(c)).Font.Bold = True
  Next c
  End Sub

Thank you so much your reply and time you spent for solving my request.

Good day and good luck!

Kind Regards,
Moti

 

[Peter_SSs]

I Just added 1 line to make font bold.

That is fine but this would be slightly more efficient as Instr and Len only need to be calculated once for each cell

Sub RedFont_v3()
  Dim c As Range

  For Each c In Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    With c.Characters(InStr(1, c, "|") + 1, Len(c)).Font
      .Color = vbRed
      .Bold = True
    End With
  Next c
End Sub

 

[motilulla]

That is fine but this would be slightly more efficient as Instr and Len only need to be calculated once for each cell

Sub RedFont_v3()
  Dim c As Range

  For Each c In Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    With c.Characters(InStr(1, c, "|") + 1, Len(c)).Font
      .Color = vbRed
      .Bold = True
    End With
  Next c
End Sub

Peter_SSs, yes this one is perfect! with mine added row it was screen flickering.​

I appreciate your help. Good day and good luck!

Kind Regards,
Moti

 

[Peter_SSs]

it was screen flickering.

Might be best anyway then to turn off screen updating while the process is being done.

Sub RedFont_v3()
  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In Worksheets("Sheet3").Range("B6:B25")  '<~~ *** Check sheet & range ***
    With c.Characters(InStr(1, c, "|") + 1, Len(c)).Font
      .Color = vbRed
      .Bold = True
    End With
  Next c
  Application.ScreenUpdating = True
End Sub