Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In Excel there are several ways to dynamically color a full cell based off of its value, but is there any way to dynamically color only part of the cell based on its value?

For example, say I am building a report that looks something like the following:

 _________________________
|       |    Dec    | Nov |
|_______|___________|_____|
|Gross R| $75 (-25%)| $100|
|_______|___________|_____|
|Net Inc| $55 (+10%)| $50 |
|_______|___________|_____|

In this scenario I only wish to color the percentage values (-25%) and (+10%), not the dollar values $75 and $55 which are also in the cell. Adding to the problem is the coloring should be dynamic (green for positive, red for negative values), and these cells are references (so manual coloring is off the table).

I have tried using the built in TEXT() function, but that did not work either. Specifically I tried =TEXT(A1,"$##")&" "&TEXT(A2,"[Green]0%;[Red](-0%)") where A1 is the cell reference to the dollar amount and A2 is the cell reference to the percentage delta.

The frustrating thing is that the custom formatting [Green]0%;[Red](-0%) works just fine when applied to the entire cell (via the custom number formatting section), but when applied via the TEXT() function it stops working. So, how can I custom color a partial value within a cell?

share|improve this question
1  
Excel functions can't do it, conditional formatting applies only to whole cells, and it sounds like you've exhausted the possibilities with custom number formats. It might be doable in VBA, though. (For a simple example, take a look here. I'm curious, though. How are you getting two numbers in one cell, building them up as strings, perhaps? –  chuff Jan 5 '13 at 7:12
    
@chuff Yes, I was building them up as strings so I could get around the grid limitations of Excel (fixed width column size for each row). –  Moses Jan 7 '13 at 21:18

2 Answers 2

up vote 3 down vote accepted

There were two approaches I discovered to get around this problem, and neither where really optimal.

The first approach was breaking the strings into two separate columns, that way I could use one of the earlier described custom formatting to set its color. This isn't an ideal solution because I had to compromise on the "look and feel" of the report in order to accommodate that extra column.

The second approach is through using VBA/macros, which though I opted to avoid in this particular scenario, would have been up to the task. While I won't print out the entire working code, it essential boils down to this:

  1. Find cell you wish to adjust (either through ActiveCell or a loop)
  2. Use Instr function to find location in string where you wish to modify color
  3. If text length is variable, use Instr again to find location in string where you wish to stop the color
  4. Use the Characters(start, length) function to highlight the exact characters you want to modify, passing in the values found earlier.
  5. Change the color with Font.Color = RGB(r,g,b)
share|improve this answer

An example using a macro can be found here:

Macro to colour part of the text in cells in Excel

Excel Macros - For Loop to Colour Part of Cells

Use an Excel macro that contains a for loop to loop through rows of weather data and colour part of the cell text red if it contains the word hot and blue if it contains the word cool:

Instructions

  1. Click on the Developer tab in Excel
  2. Click on the Visual Basic icon and copy the macro text below into 1the code window
  3. Click the Excel icon to switch back to the Excel view
  4. Click on the Macros icon, select the macro called TextPartColourMacro and click run

The Completed Macro:

Sub TextPartColourMacro()

' Declarations and Initialisation
Dim Row As Integer, Col As Integer
Dim CurrentCellText As String
Col = 1

' Loop Through Rows 2 to 5
For Row = 2 To 5

    ' Get Text in Current Cell
    CurrentCellText = ActiveSheet.Cells(Row, Col).Value

    ' Get the Position of the Text Hot and Cool
    HotStartPosition = InStr(1, CurrentCellText, "Hot")
    CoolStartPosition = InStr(1, CurrentCellText, "Cool")

    ' Colour the Word Hot Red
    If HotStartPosition > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(HotStartPosition, 3).Font.Color = RGB(255, 0, 0)
    End If

    ' Colour the Word Cool Blue
    If CoolStartPosition > 0 Then
        ActiveSheet.Cells(Row, Col).Characters(CoolStartPosition, 4).Font.Color = RGB(0, 0, 255)
    End If

Next Row
End Sub
share|improve this answer
2  
Super User's purpose is to build a knowledgebase rather than a collection of links to answers elsewhere. External links can break, in which case your answer would have no value. Please include the essential information in your answer and use the link for attribution and further reading. –  fixer1234 Jun 20 at 22:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.