format a specific letter by code
 

Hi there,
when text is copied to a new sheet by code, I need a specific letter (v)
made bold and red to indicate to the user that the words on either side of it
are seperate.
A good analogy would be football teams, so for instance:
Real Madrid v Real Betis. Here the 'v' between the names would be red and
bold.
Valencia v Villa Real. I only want the 'v' seperating the team names to be
formatted as above.
Any ideas?
Thanks.
--
Traa Dy Liooar

Jock

format a specific letter by code
 

Sub findvSAS()
For Each c In Range("a1:a22")
If InStr(c, " v ") Then
'MsgBox c.Row
x = InStr(c, " v ")
'MsgBox x
With c.Characters(Start:=x + 1, Length:=1).Font
..Bold = True
..ColorIndex = 3
End With
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jock" wrote in message
...
Hi there,
when text is copied to a new sheet by code, I need a specific letter (v)
made bold and red to indicate to the user that the words on either side of
it
are seperate.
A good analogy would be football teams, so for instance:
Real Madrid v Real Betis. Here the 'v' between the names would be red and
bold.
Valencia v Villa Real. I only want the 'v' seperating the team names to be
formatted as above.
Any ideas?
Thanks.
--
Traa Dy Liooar

Jock

format a specific letter by code
 

You left a lot of detail out, so I constructed a general "copy data, format
the v" macro for you to use, just change the values in the four Const
statements to match your actual setup...

Sub CopyDataHighlightV()
Dim TextToCopy As String
Dim X As Long, Vposition As Long, StartRow As Long, LastRow As Long
Const DataSheet As String = "Sheet1"
Const StartDataCell As String = "A1"
Const CopySheet As String = "Sheet2"
Const StartCopyCell As String = "B2"
'....
'....
With Worksheets(DataSheet).Range(StartDataCell)
StartRow = .Row
LastRow = .End(xlDown).Row
For X = 0 To LastRow - StartRow
TextToCopy = .Offset(X).Value
Vposition = InStr(TextToCopy, " v ")
With Worksheets(CopySheet).Range(StartCopyCell).Offset( X)
.Value = TextToCopy
With .Characters(Vposition + 1, 1).Font
.Bold = True
.ColorIndex = 3
End With
End With
Next
End With
End Sub

--
Rick (MVP - Excel)



"Jock" wrote in message
...
Hi there,
when text is copied to a new sheet by code, I need a specific letter (v)
made bold and red to indicate to the user that the words on either side of
it
are seperate.
A good analogy would be football teams, so for instance:
Real Madrid v Real Betis. Here the 'v' between the names would be red and
bold.
Valencia v Villa Real. I only want the 'v' seperating the team names to be
formatted as above.
Any ideas?
Thanks.
--
Traa Dy Liooar

Jock

format a specific letter by code
 

off home now Don but will try in the mornin'.
Looks the business though!

Thanks.
--
Traa Dy Liooar

Jock


"Don Guillett" wrote:

Sub findvSAS()
For Each c In Range("a1:a22")
If InStr(c, " v ") Then
'MsgBox c.Row
x = InStr(c, " v ")
'MsgBox x
With c.Characters(Start:=x + 1, Length:=1).Font
..Bold = True
..ColorIndex = 3
End With
End If
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jock" wrote in message
...
Hi there,
when text is copied to a new sheet by code, I need a specific letter (v)
made bold and red to indicate to the user that the words on either side of
it
are seperate.
A good analogy would be football teams, so for instance:
Real Madrid v Real Betis. Here the 'v' between the names would be red and
bold.
Valencia v Villa Real. I only want the 'v' seperating the team names to be
formatted as above.
Any ideas?
Thanks.
--
Traa Dy Liooar

Jock

.

format a specific letter by code
 

I guess to make sense as a macro, I should assume the two names are in
adjacent columns (my original code assumed the text was already concatenated
on the DataSheet). This macro will start by taking the names from the
StartDataCell and the cell to its right, concatenate them, then put that
concatenated text on the CopySheet starting in the StartCopyCell and then
continue doing that on down the two columns on the DataSheet...

Sub CopyDataHighlightV()
Dim TextToCopy As String
Dim X As Long, Vposition As Long, StartRow As Long, LastRow As Long
Const DataSheet As String = "Sheet1"
Const StartDataCell As String = "D9"
Const CopySheet As String = "Sheet2"
Const StartCopyCell As String = "C3"
'....
'....
With Worksheets(DataSheet).Range(StartDataCell)
StartRow = .Row
LastRow = .End(xlDown).Row
For X = 0 To LastRow - StartRow
TextToCopy = .Offset(X).Value & " v " & .Offset(X, 1).Value
Vposition = Len(.Offset(X).Value) + 1
With Worksheets(CopySheet).Range(StartCopyCell).Offset( X)
.Value = TextToCopy
With .Characters(Vposition + 1, 1).Font
.Bold = True
.ColorIndex = 3
End With
End With
Next
End With
End Sub

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
You left a lot of detail out, so I constructed a general "copy data,
format the v" macro for you to use, just change the values in the four
Const statements to match your actual setup...

Sub CopyDataHighlightV()
Dim TextToCopy As String
Dim X As Long, Vposition As Long, StartRow As Long, LastRow As Long
Const DataSheet As String = "Sheet1"
Const StartDataCell As String = "A1"
Const CopySheet As String = "Sheet2"
Const StartCopyCell As String = "B2"
'....
'....
With Worksheets(DataSheet).Range(StartDataCell)
StartRow = .Row
LastRow = .End(xlDown).Row
For X = 0 To LastRow - StartRow
TextToCopy = .Offset(X).Value
Vposition = InStr(TextToCopy, " v ")
With Worksheets(CopySheet).Range(StartCopyCell).Offset( X)
.Value = TextToCopy
With .Characters(Vposition + 1, 1).Font
.Bold = True
.ColorIndex = 3
End With
End With
Next
End With
End Sub

--
Rick (MVP - Excel)



"Jock" wrote in message
...
Hi there,
when text is copied to a new sheet by code, I need a specific letter (v)
made bold and red to indicate to the user that the words on either side
of it
are seperate.
A good analogy would be football teams, so for instance:
Real Madrid v Real Betis. Here the 'v' between the names would be red and
bold.
Valencia v Villa Real. I only want the 'v' seperating the team names to
be
formatted as above.
Any ideas?
Thanks.
--
Traa Dy Liooar

Jock