Code amendment for changing text colour in a cell

asylum

Board Regular
Joined
Dec 2, 2003
Messages
243
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
Hi

Try:

Code:
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

Board Regular
Joined
Dec 2, 2003
Messages
243
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

Board Regular
Joined
Dec 2, 2003
Messages
243
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,881
I'm glad it helped. BTW, just tested and the code works ok for me as I posted.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,667
Messages
5,597,458
Members
414,145
Latest member
lonnie451

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