Saturday, September 15, 2007

Excel VBA - Change Font Color for Part of Text

Formatting Text (Cell Contents) Partially using Excel VBA

Sometimes, you need to differentiate some parts of text in an Excel cell. This can be done using formatting those characters.

Here is the way that can be done using VBA using the Characters property


Sub Color_Part_of_Cell()
'Print ActiveCell.Characters.Count
With ActiveCell.Characters(2).Font
.Color = RGB(36, 182, 36)
End With

With ActiveCell.Characters(2, 2).Font
.Color = RGB(36, 182, 36)
End With

With ActiveCell.Characters(, 2).Font
.Color = RGB(36, 182, 36)
End With

End Sub

The output of the above is



Syntax

expression.Characters(Start, Length)

expression Required. An expression that returns an object in the Applies To list.

Start Optional Variant. The first character to be returned. If this argument is either 1 or omitted, this property returns a range of characters starting with the first character.

Length Optional Variant. The number of characters to be returned. If this argument is omitted, this property returns the remainder of the string (everything after the Start character)


The same characters object can be used to make a character Bold / Italic

10 comments:

  1. Anonymous10:54 AM

    Hi
    can you extract a text string from the excel cell, which has a different color? The text in the cells is in black and blue, but could not figure out how to make extraction of the blue or black work.
    Thank you.

    ReplyDelete
  2. Hi

    You can try something similar to the following to extract colored text to a separate string

    Sub Extract_Color_Part_of_Cell()

    Dim sColoredText

    For i1 = 1 To ActiveCell.Characters.Count
    If (ActiveCell.Characters(i1, 1).Font.Color <> vbAutomatic) Then
    sColoredText = sColoredText & ActiveCell.Characters(i1, 1).Text
    End If
    Next i1
    End Sub

    ReplyDelete
  3. Anonymous3:35 AM

    Hello,
    Can this be modified to show a trigger instead of a certain character location. For example, if the name John is in the cell, then everything from John to the end of the string be another font color.

    ReplyDelete
  4. Anonymous1:43 PM

    So glad I found this. You initial code and response post were exactly what I was looking for. Thanks!

    ReplyDelete
  5. This will not work if the cell has a custom number format. Change to a non-custom format (eg General) and it works as expected.
    Paul Wright
    www.excelexpert.com

    ReplyDelete
  6. Anonymous7:06 PM

    Hello Shasur. This is what I need. The report template I'm working on has built in macros. I don't know how exactly to implement the codes you wrote. For example, how can I modify your code to point to a specific cell in my report? Also, when I insert your codes as part of a subset of an existing Sub and ran the macro, it tells me that it was expecting an End Sub. I'm clueless as to what to do since I'm very green at Excel VBA. Any clues you could provide would be greatly appreciated.

    ReplyDelete
  7. Ok, but How can I do to write in a cell with different colors ?

    ReplyDelete
  8. srm_06217:58 PM

    Hi,

    In a project that I am presently working on, I have multiple text values in 1 cell and depending on a pre defined condition, I need to colour code those different values - but all in 1 cell. I understand the syntax: With Cells(1, 2).Characters(Start:=ii, Length:=Len(ss)).Font .Color = -11489280

    but the problem that I am facing is that regardless of what the condition instructs, once the next value enters the same cell with a different colour, the previous value changes it's colour to the same as the one just entered. Thus all the values have the same colour, except maybe the last value.

    Please advise. Thanks.

    ReplyDelete
  9. You'll need to create "event handlers." You want to save the existing color scheme BEFORE the user types something new in the cell, and then re-apply the color scheme after he hits enter.

    Suppose the cell you are interested in is Sheet2!E25. Open up visual basic, and on the top left side of the window you will see a list of all open workbooks. Each is named "VBAProject (SRMDataEntry)" where SRMDataEntry is the name of your workbook. If there is a plus sign next to your workbook, click on it to expand it. You will see a list of all the worksheets and all the modules in the SRMDataEntry workbook.

    Find the spreadsheet that has the cell you want to keep colored in a certain way. Right click on that worksheet & select "view code." This is where you put event handler code.

    Try playing around with two particular events -- worksheet_change and worksheet_selectionchange, like this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    msgbox("You selected a new cell with your mouse or keyboard - and the cell you selected is " & target.address )
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    msgbox("You entered new data in cell " & target.address )
    End Sub

    ReplyDelete
  10. once you get the hang of how events work, the solution to your problem starts to become clear (or at least clearer). Instead of popping up msgboxes, you automatically reapply your preferred formatting whenever the contents change in your special input cell. Here's an example:

    Private Sub Worksheet_Change(ByVal Target As Range)
    if target.address = "$b$27" then
    target.Characters(1,5).font.color = rgb(255,0,0)
    'sets 5 characters starting with first character to red
    'i.e. sets characters 1-5 to red
    target.Characters(6,5).font.color = rgb(0,255,0)
    'sets 5 characters starting with 6th character to green
    'i.e. sets characters 6-10 to green
    target.Characters(11).font.color = rgb(0,255,0)
    'sets ALL remaining characters starting with 11th character to blue
    'i.e. sets characters 11, 12, 13 ... to blue
    End if
    End Sub

    The only downside of the above is that it will crash if the user enters less than 11 characters. Better would be to create a hidden spreadsheet named "Secret", and put your "color template" in cell A1 in Secret. Then you can do as follows:

    Private Sub Worksheet_Change(ByVal Target As Range)
    if target.address = "$b$27" then
    for i = 1 to min(target.characters.count, _
    range("Secret!A1").characters.count)
    target.Characters(i).font.color = _
    range("Secret!A1").characters(i,1).font.color
    next i
    End if
    End Sub
    If the entry in B27 is longer than the entry in Secret!A1, then the last iteration of the loop will set target.characters(n).font.color to the color of the last character in Secret!A1. target.characters(n).font.color sets the color of ALL characters starting at n.

    If you wanted to use the contents of E27 as your color scheme, rather than having a predetermined color scheme, you would use the Worksheet_SelectionChange event to tip off the program that someone has clicked on E27 -- BEFORE he makes any changes. Like this:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    if target.address = "$E$27" then _
    range("Secret!A1") = range("E27")
    End Sub



    Hope that helps.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group