Changing the font colour of part of a cell.

Sefty

New Member
Joined
Apr 5, 2022
Messages
16
Office Version
  1. 365
Platform
  1. Windows
1649906745425.png


With VBA, if all the words to colour were single words, then this macro would do the job; elsewise, would all the cells begin with "Meeting" ?
VBA Code:
Option Explicit
Sub ChangeCharacterColor()
    Dim sChar  As Integer
    Dim lChar  As Integer
    Dim x      As Long
    For x = 1 To Cells(Rows.Count, "A").End(xlUp).Row   'from row 1 to last used row
        sChar = InStrRev(Cells(x, "A"), " ") + 1  'find last space
        lChar = Len(Cells(x, "A")) - sChar + 1    'number of characters to end
        Cells(x, "A").Characters(start:=sChar, Length:=lChar).Font.Color = -16776961
    Next x
End Sub

Hello
Need to help, if I'm looking for something to automatically change the colour of part of the text in a cell. So for example: Cell A1 contains "01.01", "01.01" 5 character in front of the sentence, I want to change the colour of the word "01.01", "01.02", "01.03" to be in red. if I use the code like below, the result is like the picture doesn't match.

VBA Code:
Sub ChangeCharacterColor()
Dim sChar As Integer
Dim lChar As Integer
Dim x As Long
For x = 1 To Cells(Rows.Count, "A").End(xlUp).Row 'from row 1 to last used row
sChar = InStrRev(Cells(x, "A"), " ") - 4 'find last space
lChar = Len(Cells(x, "A")) + sChar - 4 'number of characters to end
Cells(x, "A").Characters(Start:=sChar, Length:=lChar).Font.Color = -16776961
Next x
End Sub

Thank you...
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,756
Office Version
  1. 2016
to color first 5 characters in column A:
VBA Code:
Sub ChangeCharacterColor()
Dim cell As Range
For Each cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    cell.Characters(1, 5).Font.Color = -16776961
Next
End Sub
 

Sefty

New Member
Joined
Apr 5, 2022
Messages
16
Office Version
  1. 365
Platform
  1. Windows
to color first 5 characters in column A:
VBA Code:
Sub ChangeCharacterColor()
Dim cell As Range
For Each cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    cell.Characters(1, 5).Font.Color = -16776961
Next
End Sub
Thank you master, the code is success....
 

Forum statistics

Threads
1,175,533
Messages
5,897,961
Members
434,688
Latest member
vi28

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