VBA to change color of partial text

[afeldsh]

Hello,

I need help with VBA to change color of partial text.

I am working with 2 worksheets. 1 worksheet is named "Reference Information", column A contains specific text. The list can grow over time.

Second worksheet is "Sourcing". I would like for VBA to look at the data in column Z of this worksheet, and if any of the text in this column, starting with 2nd row and up contains any text from column A of the "Reference Information" worksheet, to change color of that text only in red font, leaving remaining text font color as is.

Is this possible?

TIA
Alex

 

[bebo021999]

Or, try this code

Option Explicit
Sub test()
Dim ce As Range, ce2 As Range, refe As Range
Dim pos&
With Sheets("Reference") 'range of reference cells from A2 to last used cell
    Set refe = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
Sheets("Sourcing").Activate
For Each ce In Range("Z2:Z" & Cells(Rows.Count, "Z").End(xlUp).Row) 'suppose column Z contents cells those need to color
    ce.Font.Color = vbBlack ' set font color back to default (black)(if any)
    For Each ce2 In refe ' get through each ref text
        pos = InStr(1, ce, ce2) 'find the text in Z
        If pos > 0 Then ce.Characters(pos, Len(ce2)).Font.Color = vbRed 'if found then set red color
    Next
Next
End Sub

 

[afeldsh]

Or, try this code

Option Explicit
Sub test()
Dim ce As Range, ce2 As Range, refe As Range
Dim pos&
With Sheets("Reference") 'range of reference cells from A2 to last used cell
    Set refe = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
End With
Sheets("Sourcing").Activate
For Each ce In Range("Z2:Z" & Cells(Rows.Count, "Z").End(xlUp).Row) 'suppose column Z contents cells those need to color
    ce.Font.Color = vbBlack ' set font color back to default (black)(if any)
    For Each ce2 In refe ' get through each ref text
        pos = InStr(1, ce, ce2) 'find the text in Z
        If pos > 0 Then ce.Characters(pos, Len(ce2)).Font.Color = vbRed 'if found then set red color
    Next
Next
End Sub

This works as well. Thank you!

 

[Peter_SSs]

I am not sure if it matters what version of excel I am using

That is always a great help to people trying to assist you. Please update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

@afeldsh
I see that you have 'Liked' my post - but done nothing about updating your profile to show your Excel version. Is there a problem with that?

 

[afeldsh]

@afeldsh
I see that you have 'Liked' my post - but done nothing about updating your profile to show your Excel version. Is there a problem with that?

I have updated my profice to show excel version. Thank you!

 

[Peter_SSs]

I have updated my profice to show excel version.

Great! Thanks for that, it should assist helpers provide the best solutions for you if you have more questions in the future.