Format Cell Colour/Font with VBA
 

Hi,

I'm a bit of a beginner and I'm trying to write a macro to format a
cell that will change the colour and place in italics only a set
selection of the cell.

I want to select the section of the cell that occurs after two "//"
characters, and change the colour and put in italics the following
characters of the cell.

(So: "This is an example // this is the bit that needs to be formatted"
would be formatted to show the "This is an example" section as normal
text, and the "// this is the bit that needs to be formatted" would be
red italics.)

The problem is that the "//" doesn't occur at any set point in the cell
(ie, may be the 5th character or the 10th character etc) and I can't
seem to work out a way to change only that selection without actually
knowing in advance where it may occur...

This would then loop through the entire worksheet and stop when all
occurances have been reformatted.

I've searched extensively for something similar to start from but no
luck, any help would be muchly appreciated!

Thanks
Ren

Format Cell Colour/Font with VBA
 

If the cells contain text i.e not the results of formulae then you could
try:

Sub Frmt()
Dim theText As String
Dim cell As Range
For Each cell In Sheets("Sheet1").UsedRange.SpecialCells _
(xlCellTypeConstants)
theText = cell.Text
If InStr(1, theText, "//") 0 Then
With cell.Characters(InStr(1, theText, "//"), 255)
.Font.Italic = True
.Font.ColorIndex = 3
End With
End If
Next cell
End Sub

Hope this helps
Rowan

Ren wrote:
Hi,

I'm a bit of a beginner and I'm trying to write a macro to format a
cell that will change the colour and place in italics only a set
selection of the cell.

I want to select the section of the cell that occurs after two "//"
characters, and change the colour and put in italics the following
characters of the cell.

(So: "This is an example // this is the bit that needs to be formatted"
would be formatted to show the "This is an example" section as normal
text, and the "// this is the bit that needs to be formatted" would be
red italics.)

The problem is that the "//" doesn't occur at any set point in the cell
(ie, may be the 5th character or the 10th character etc) and I can't
seem to work out a way to change only that selection without actually
knowing in advance where it may occur...

This would then loop through the entire worksheet and stop when all
occurances have been reformatted.

I've searched extensively for something similar to start from but no
luck, any help would be muchly appreciated!

Thanks
Ren

Format Cell Colour/Font with VBA
 

Perfect!! Thanks Rowan - this was driving me bonkers trying to work it
out ;)
(It's probably easy too).... I'll learn!

Ren

Format Cell Colour/Font with VBA
 

You're welcome.

Ren wrote:
Perfect!! Thanks Rowan - this was driving me bonkers trying to work it
out ;)
(It's probably easy too).... I'll learn!

Ren