Changing the font colour of part of a cell.

[Nyge2k3]

Hello

I'm looking for something to automatically change the colour of part of the text in a cell. This can either be immediately (like conditional formatting) or by using VBA and running a macro to do all of them at once.

So for example: Cell A1 contains "Meeting Leeds", I want to change the colour of the word "Leeds" to be in red. I would also want to change the colour to red if it said Bristol, London, Birmingham, Glasgow etc.

Is this something that can be done?

Many Thanks

 

[rollis13]

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" ?

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

 

[Nyge2k3]

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" ?

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

Thank you. I will give that a try.

The text will be different each time and completely different from the example, it's more the end result I'm looking for.

 

[KOKOSEK]

The text will be different each time and completely different from the example, it's more the end result I'm looking for.

If cell contains result of formula, imho, is impossible to colouring part of this result.

 

[Nyge2k3]

If cell contains result of formula, imho, is impossible to colouring part of this result.

Hello, It's not the result of a formula.

 

[Peter_SSs]

Test this with a copy of your data to see if it does what you want.
I have made a list of 'words of interest' in column D and processing text in column A to highlight the relevant words.

Sub Highlight_Words()
  Dim RX As Object, M As Object
  Dim a As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "\b(" & Join(Application.Transpose(Range("D2", Range("D" & Rows.Count).End(xlUp)).Value), "|") & ")\b"
  Application.ScreenUpdating = False
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    .Font.ColorIndex = xlAutomatic
    a = .Value
    For i = 1 To UBound(a)
      For Each M In RX.Execute(a(i, 1))
        .Cells(i).Characters(M.firstindex + 1, M.Length).Font.Color = vbRed
      Next M
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

This is my sample data

Nyge2k3.xlsm
	A	B	C	D
1	Text			Highlight These
2	Meeting Leeds			Bristol
3	Travel to London next week			Leeds
4	Travel to Londoderry, Leeds, Oxford and Bristol			London
5				Oxford
6	Meeting Manchester
7	Oxford meeting
Sheet1

This is the result after running the above code.