Results 1 to 6 of 6
  1. #1
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Multiple text colors within a single cell (Excel 2000 SR1)

    I know it is possible to format the text in any given cell to be more than one color, but I am dealing with a schedule that promises to change weekly, and it will be maintained by someone at a remote location, and I have no idea of who it will be and their comfort level with Excel. To be honest, I should be able to come up with a macro for it, but I am tired enough and busy enough (aren't we all?) and inexperienced enough that I'm not ashamed to beg.
    I can assign the macro to a toolbar button (I know...ooooohhhh) but I really have no clue about the macro.

    Thanks a bunch...as always!

    Don
    Have a cookie -

    Don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple text colors within a single cell (Excel 2000 SR1)

    If you want a macro, you'll have to specify what it has to do. Just "format the text in any given cell to be more than one color" is not enough to go by. Do you want the word "Moore" to be red, whatever its position in the text, or do you want the last 10 characters to be green, or ...?

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Multiple text colors within a single cell (Excel 2000 SR1)

    This might give you some ideas. It colors each word in the sentence a different color. Highlight the selection and run the macro.

    I used 10 colors: Change the array size for less or more colors, change the ColorIndex value I stored in the array to change colors.

    If the word has more than defined number of colors it loops thru (eg if there are 11 words, word 11 is the same color as word 1.

    A "word" is defined as a space to a space, so extra spaces will not "screw it up, but it will use a color to color nothing.

    Steve

    <pre>Option Explicit
    Sub ColorWords()
    Dim iColor(10) As Integer
    Dim sSentence As String
    Dim iNumWords As Integer
    Dim lStart As Long
    Dim lEnd As Long
    Dim lLength As Long
    Dim rCell As Range
    Dim x As Integer

    iColor(0) = 1 'black
    iColor(1) = 3 'Red
    iColor(2) = 4 'light Green
    iColor(3) = 5 'Blue
    iColor(4) = 6 'Yellow
    iColor(5) = 7 'Magenta
    iColor(6) = 8 'Cyan
    iColor(7) = 9 'Brown
    iColor(8) = 10 'Dark Green
    iColor(9) = 11 'Dark Blue

    For Each rCell In Selection
    sSentence = rCell.Value
    iNumWords = Len(sSentence) - _
    Len(Application.WorksheetFunction.Substitute _
    (sSentence, " ", "")) + 1
    sSentence = sSentence + " "
    lStart = 1

    For x = 0 To iNumWords - 1
    lEnd = InStr(lStart, sSentence, " ")
    lLength = lEnd - lStart

    rCell.Characters(Start:=lStart, Length:=lLength). _
    Font.ColorIndex = iColor(x Mod UBound(iColor))

    lStart = lEnd + 1
    Next x
    Next rCell
    End Sub
    </pre>


  4. #4
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Re: Multiple text colors within a single cell (Excel 2000 SR1)

    OK, I've actually SEEN the spreadsheet in its 'final' form and it was WAY too complicated. There was no need for multiple lines of information within the same cell, so that problem was solved quite easily. My only question now is, is there a way to set up a table to indicate certain text values that will be populated at various places on this spreadsheet at different times, so that each value (some examples are we1, we2) - so that each time 'we1' appears, the text is automatically made green? That would fit the bill.

    Thanks -

    Don
    Have a cookie -

    Don

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple text colors within a single cell (Excel 2000 SR1)

    Hi,

    Have a look at conditional formatting.

    Say you wish Cell B1 to turn green when cell A1 contains "we1".

    - select cell B1
    - Format, conditional format
    - in the lefthand dropdown select "Formula Is"
    - in the formula box, type: =A1="we1"
    - click the format button and choose a green fill (or whatever else you like)
    - OK, OK.

    So this colours a cell based on a value of another cell. Of course you can also change the format based on the value of the cell itself. Then most of the time the option "Cell Value Is" of the lefthand dropdown gives sufficient options.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger djmoore's Avatar
    Join Date
    Feb 2001
    Location
    New Jersey, USA
    Posts
    371
    Thanks
    21
    Thanked 1 Time in 1 Post

    Re: Multiple text colors within a single cell (Excel 2000 SR1)

    I actually had to rule out conditional formatting, since that only allows for 3 separate conditions and this spreadsheet has 7 (and possibly more in the future).
    Have a cookie -

    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •