Code amendment for changing text colour in a cell

[asylum]

Hello,

I have the below text that looks in a range of cells and changes BH: to bold if it finds it present. However I want to amend this to achieve three things:

The BH: is always the first thing in the cell (if present) and is followed by some text and a comma then some other text ie:

BH: string1, blah, blah, blahblah

What I want to do is, if BH: starts the cell then to-

1) make everthing before (and including) the first comma Bold, (in black already)
2) turn all text after the comma to red,
3) Highight the cell in light grey,

any thoughts? Thanks

Sub BH()

Dim R As Range

Sheets("one").Select

For Each R In Range("d1:d380")
If InStr(1, R.Value, "BH:", 1) Then
For i = 1 To Len(R.Value)
If R.Characters(Start:=i, Length:=3).Text = "BH:" Then _
R.Characters(Start:=i, Length:=3).Font.Bold = True
Next i
End If
Next R

End Sub

 

[pgc01]

Hi

Try:

Sub BH()
Dim R As Range
Dim j As Long
 
For Each R In Worksheets("one").Range("d1:d380")
    If R.Value Like "BH:*,*" Then
        j = InStr(R.Value, ",")
        R.Characters(Start:=1, Length:=j).Font.Bold = True
        R.Characters(Start:=j + 1).Font.Color = vbRed
        R.Interior.Color = RGB(224, 224, 224)
    End If
Next R
End Sub

 

[asylum]

Hi,

small problem it actaully turns the cell white, but thats an easy one to fix, however its turning the whole cells text bold black here, not half bold black and teh rest normal red??

 

[asylum]

got it! (see below) tahnks for your help!

Sub BH()
Dim R As Range
Dim j As Long

For Each R In Worksheets("Calendar").Range("d1:d380")
If R.Value Like "BH:*,*" Then
j = InStr(R.Value, ",")
R.Characters(Start:=1, Length:=j).Font.Bold = True
R.Characters(Start:=j + 1).Font.ColorIndex = 3
R.Interior.Color = RGB(224, 224, 224)
End If
Next R

End Sub

 

[pgc01]

I'm glad it helped. BTW, just tested and the code works ok for me as I posted.