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

1 Answer 1

up vote 1 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

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.