format a specific letter by code

[Jock]

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.

 

[Don Guillett]

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

 

[Rick Rothstein]

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

 

[Jock]

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

Thanks.

 

[Rick Rothstein]

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)

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