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
0
Utf
4/29/2010 3:19:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
581 Views

Similar Articles

[PageSpeed] 29

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
dguillett@gmail.com
"Jock" <Jock@discussions.microsoft.com> wrote in message 
news:D1EB84BB-2CD3-4D88-9FB3-5D0EEA316352@microsoft.com...
> 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 

0
Don
4/29/2010 3:44:44 PM
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" <Jock@discussions.microsoft.com> wrote in message 
news:D1EB84BB-2CD3-4D88-9FB3-5D0EEA316352@microsoft.com...
> 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 

0
Rick
4/29/2010 3:49:24 PM
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
> dguillett@gmail.com
> "Jock" <Jock@discussions.microsoft.com> wrote in message 
> news:D1EB84BB-2CD3-4D88-9FB3-5D0EEA316352@microsoft.com...
> > 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 
> 
> .
> 
0
Utf
4/29/2010 4:27:01 PM
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" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message 
news:OFC7pO75KHA.5548@TK2MSFTNGP04.phx.gbl...
> 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" <Jock@discussions.microsoft.com> wrote in message 
> news:D1EB84BB-2CD3-4D88-9FB3-5D0EEA316352@microsoft.com...
>> 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
> 
0
Rick
4/29/2010 4:51:46 PM
Reply:

Similar Artilces:

Country Codes #2
I can't for the life of me find the GP menu item that maintains the codes. Can anyone point me to it? Open the Country Code Maintenance window. (Tools >> Setup >> Company >> Country Codes) The Country Code Maintenance window will be available only if you have marked to enable Intrastat tracking in the Company Setup Options window. See Help for more info -- Elisabeth "Ken Denman" wrote: > I can't for the life of me find the GP menu item that maintains the codes. > Can anyone point me to it? If you don't have Intrastat Tracking turned on ...

formatting #2
I have a large worksheet that I need to change the text to all caps. I have tried using the UPPER function but it will only work in one cell. How to I get the entire worksheet to become all caps? Help! Hi see http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany Jackie wrote: > I have a large worksheet that I need to change the text to > all caps. I have tried using the UPPER function but it > will only work in one cell. How to I get the entire > worksheet to become all caps? Help! Hi Jackie If you have trouble using macros you can do this ...

Conditional Formatting #89
I need to know how to make a block turn RED if the date exceeds a year. Thanks! Do you mean if the data exceeds one year from today? If so, select the target cell, go to Format > Conditional Formatting, choose Formula Is, and put: =$A$1<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) Change $A$1 to your actual cell reference. HTH Jason Atlanta, GA >-----Original Message----- >I need to know how to make a block turn RED if the date exceeds a year. Thanks! >. > Try this in - Conditional Format - Formula Is: =DATE(YEAR(E1)+1,MONTH(E1),DAY(E1))<=TODAY() If E1 was ...

Zoom percentage and formatting palette
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel 1. How do you make sure that Word opens to a certain zoom percentage (140% for me) no matter what Word document I open? <br><br>2. How can I get the formatting palette to not be a floating box but to be incorporated right into the toolbar? <br><br>Thanks from a new user On 24/05/2010 03:00, Jerdog201@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel 1. How do you make sure that Word opens to a certain zoom > percentage ...

capital letters
Hi, I have a form where in one column of cells, either Y or N is supposed to be entered. Is there a way to convert a "y" to "Y" so that they look a bit tidier? This conversion would occur automatically no matter if they entered a "y" or "Y". Thanks for any ideas. Harold Harold, Although Autocorrect could be used, it would kick in any time you typed a y or n, even in other Office programs. Probably the best is an event-fired macro that makes the changes only when you're in the column of interest. Paste this into the sheet module in the ...

report format portrait and lanscape
Is it possible to have one command to change report between portait or lanscape? If not, probably the best solution will be 2 reports. Your information is great appreciated, Your report is probably designed in portrait. This means it is designed to print 8-1/2" - the left and right margins wide. If you change the printout to landscape it is still going to print less than 8-1/2" wide on the left side of the paper leaving more than 3-1/2" blank on the roght side of the paper. To fill the paper you would have to write code to reposition and stretch out each con...

Colour code messages only to me
Hi I am running Outlook XP. There is a rule that can be set to colour code emails that are sent only to me, but what it actually does is colour code emails that only have me in the To: line of the email. If an email is sent to me and CC's in someone else, the rule treats this as a mail sent only to me. How do I set the rule so that it colour codes a message sent solely to me? Thanks in advance ...

Community bank's new software doesn't export in my format
I am still using Money98 which does all of the little tasks I need (no investments at my age). Two national banks provide exports in .qif format as a matter of course, but when my local community bank went to new (better) software, ".qif" was missing from the list of available export formats. I wrote the bank and received word that the bank's vendor didn't support Money98. The bank officer said: "My guess is the contract they have with Intuit and Microsoft stipulates they not support it as those companies want consumers to always upgrade their software.&...

Secure code in Excel & Power Point ADD-INS
Is there any way to secure code in Excel & Power Point ADD-INS, except the password defined in VBA ? No. Use VB to create COM addins. ************ Anne Troy VBA Project Manager www.OfficeArticles.com <CLarkou@gmail.com> wrote in message news:1132296881.379001.293560@o13g2000cwo.googlegroups.com... > Is there any way to secure code in Excel & Power Point ADD-INS, except > the password defined in VBA ? > Thanks a lot, these add-ins can be called in Access also, isn't? The COM add-ins can be created by Visual Basic code or it needs C++ ? You can use VB. -- ...

Question on Formats...
This ought to be quite simple to answer. Is it possible to format the contents of a combobox? On sheet1 I have text in cells A1 to A8 e.g A1 = John, A2 = Steve, A3 = Claire etc. I have a combobox on a chart that displays those names when I click the down arrow. The default is lower case, black lettering. Can I make the names appear in bold and green for example? I have tried modifying the original names but that makes no difference. If I right click the combobox and select <Format Control> I cannot see a way to format my data entries. Maybe you can't do it. And I want to ...

color coding events
I would like to color code events on my daily calendar and wonder if there is a way to change the color of text on individual events On 3/7/2010 6:27 AM, jcurtis wrote: > I would like to color code events on my daily calendar and wonder if > there is a way to change the color of text on individual events Which version of Outlook? Outlook 2007 introduced colored categories and I use them daily for color-coding events. Prior to Outlook 2007, colored labels were your option: http://office.microsoft.com/en-us/outlook/HP030848441033.aspx Additional information: htt...

Excel page format changes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello, <br><br>My client creates spreadsheets for her boss. She pushes the boundaries to get things to fit on the screen. <br> Uses A4, margins at 1.5cms and scales to 90%. <br><br>When her boss receives it by email the margins go to 2.5cms, scales to 100%. They have the same versions 12.2.4 and Leopard. They are just about to be upgraded to Snow Leopard. Any idea why this could be happening? Thanks. ...

two decimals - remove percentage sign
Hello everyone, I have percentages that i'm displaying in graphs with data labels. I'd like to remove the % sign so it is easier to view the data labels because the graphs are small, and i'd like to do it with custom formatting because there is quite a lot of data that i dont want to convert to whole numbers. I think the best way to do this is to somehow use custom formatting in format cells -->number-->custom to multiply by 100 and display it that way. is that possible? Thanks for the help! Adam ...

Print a letter from a workflow
Hi, Is it possible to run a workflow that would print a letter. In other words when an accout is created (with certain conditions), letter a is sent t the printer. then wait 2 months then letter b is sent then wait 2 months then letter c is sent. All of these letters would need to be a template (ie with name address tags) Thanks in advance On Dec 8, 7:01=A0pm, Tango <Ta...@discussions.microsoft.com> wrote: > Hi, > > Is it possible to run a workflow that would print a letter. > > In other words when an accout is created (with certain conditions), lette= r a > is ...

Office 2007 non-commercial use + Code 78F when updating
Hi All, Have a couple questions relating a Vista/Office 2007. We bought a sony vaio laptop that came pre-intalled with a trial license of office 2007. I un-installed Office completely and install a fresh copy of our partner license copy of office 2007, but when loading any office application now its shows 'Microsoft Outlook non-commercial use' and same for word,excel, etc. Anyone have any idea why this is? Second question is relating to windows updates in Vista; I get an error Code 78F when trying to install any update, and the soultions i try doesnt seem to resolve this issue....

Msgbox Code Syntax Error
I have a message box that I want to display in the OnEnter event of a List box simply to give instructions as to how the user is to enter the info. My code for the Message box isn't working and I'm getting a syntax error - I also got an error that the system is expecting an "=" in the code. I've researched it but can seem to see what I'm missing. I only want an OK button on it and then to return focus to to the control to which the code is attached. Here's the code: Private Sub lbDamagedParts_Enter() Me!Label23.Visible = True Msgbox("For th...

OL 2003; Question about two default attachments in HTMLMessage Format
This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C51E85.7F26CCA0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0010_01C51E85.7F26CCA0" ------=_NextPart_001_0010_01C51E85.7F26CCA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Outlook 2003, XPP SP2=20 Hello, When I send an e-mail message in HTML message format, the recipient = (even when I send to myself) also receives two default attachments, = image001.png and image002.png. The first image is a what I would guess a = Microsoft ...

Compile Database Code Problem
I need to convert a database to ACCDE and need to compile my VBA, however when I select Compile under the Debug menu I get the following error, "Method or data member not found". The problem is that the Sub it is identifing is a part of my database and works well when I use the database. The code that failed was the following with ".Combo2" highlighted in the second line. Private Sub Combo2_AfterUpdate() TransferSection = Me.Combo2 MsgBox "Transfer Section is " & TransferSection RunCommand acCmdRecordsGoToNew RunCommand acCmdSelectRecord...

Question about MFC source code.
Hi, all, I find that there are some redefinitions in afxHH.h. Something looks like this: #define HID_FILE_MRU_FILE1 0x1E110 #define HID_FILE_MRU_FILE1 0x1E111 // aliases: MRU_2 - MRU_16 #define HID_FILE_MRU_FILE1 0x1E112 #define HID_FILE_MRU_FILE1 0x1E113 #define HID_FILE_MRU_FILE1 0x1E114 #define HID_FILE_MRU_FILE1 0x1E115 #define HID_FILE_MRU_FILE1 0x1E116 #define HID_FILE_MRU_FILE1 0x1E117 #define HID_FILE_MRU_FIL...

How to customize error bars by VB code?
Hello NG, from a Visual Basic (6) project, I need to customize the error bar of a chart, to get a different value for each data point. In Excel, this is done with a range reference: ActiveChart.SeriesCollection(1).ErrorBar Direction:=xlY, Include:= _ xlPlusValues, Type:=xlCustom, Amount:="=Hoja1!R1C3:R2C3" but I need to do this by VB code, in order to control a OLE-Chart. Reference to the datasheet-range is not working: oGraph.SeriesCollection(1).ErrorBar Direction:=xlY, Include:=xlBoth, _ Type:=xlCustom, Amount:=oGraph.Application.DataSheet.Range("A1:A3") Any ide...

Code versus Int
Hi, Web applications often use a parameter to select a specific record. Hotmail, for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to retriev a specific mail message. Question 1) Is an Int type not prefered? Question 2) What is a smart way to generate unique random numbers? Date + number??? 051320101 = 05-13-2010-1 Thanks! Arjen Arjen (boah123@hotmail.com) writes: > Web applications often use a parameter to select a specific record. > Hotmail, for example, uses a code like "ZIaXbPiFjyPUfJG44nag9A%3d%3d" to > retriev a specific...

Zip Codes 5 digits only
Is there a way to change zip codes in a spreadsheet to only display 5 digits? Hi, Use custom cell format 00000. This will add leading zeros, if the cod is shorter than 5 digits. - Asse -- Message posted from http://www.ExcelForum.com =LEFT(zipcode,5) will strip off the extended codes. Jerry anonymous@discussions.microsoft.com wrote: > Is there a way to change zip codes in a spreadsheet to > only display 5 digits? ...

conditional formatting: separating rows with differing values
Hi, Need help with conditional fomatting. What I need: I got a worksheet with 950 rows and 5 columns. The first row contain the headers. Columns A, B, D and E contains unique values. But the column C contains text values which repeats sequentially for 6-7 rows and changes thereafter. Now I want to put up a line (using border) through the whole row dividing this transition row. My Idea: Compare C2 with C3, if equal do nothing. Then compare C3 with C4 and so on till 2 consequtive values differ. Now divide the 2 rows using the border formatting option in conditional formatting dialog box. So...

code list of UTF-8
i searching for a list of the double character encoding spezial charcters in UTF-8 such as äüö... Can any one send me link where i can find a complete list? -- best regards Rolf Brockmann Rolf Brockmann wrote: > i searching for a list of the double character encoding spezial > charcters in UTF-8 such as äüö... > Can any one send me link where i can find a complete list? See http://www.faqs.org/rfcs/rfc2279.html. Cheers, -- http://www.joergjooss.de mailto:news-reply@joergjooss.de Rolf Brockmann wrote: > i searching for a list of the double ch...

Access 2003 VB Code Export Query Failing with Access 2007 user
I have a set of excel export queries I created in an access 2003 DB. Now, some of my users have upgraded to Access 2007 and are running in to a "Parameter" error when the execute the "canned" excel export queries I created for them. My only presumption is that it is due to the fact that I have hardcoded the export function, to go to an Office 2003 Excel file, and for whatever reason, Access 2007 cannot handle this. This make sense? And if so, any suggestions? I suppose if there was an easy way to test what version of office/excel they are running, I could change...