14

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?

3
  • 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, 2013 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, 2013 at 21:18
  • It's funny that if one copy-pastes from Word to Excel it is already possible to have different colors in a cell out of the box.
    – Turo
    Jul 13, 2021 at 11:42

3 Answers 3

8

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)
1
  • 2
    Don't have enough rep to downvote, but this would have been my first: "While I won't print out the entire working code" - why not ? Pretty helpful for a forum like this.
    – hmedia1
    Apr 24, 2019 at 17:48
7

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:

Please note Hot and Cool are case sensitive for Instr.

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
2
  • 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, 2015 at 22:21
  • This is a good solution, I will be deploying it.
    – htm11h
    Jul 24, 2017 at 21:03
0

This macro will color any selected range of text containing brackets to red if the text within the brackets contains a "-" character, otherwise it will color it green. All text outside of the brackets remains unchanged.

Dim rngCell As Range
Dim CharCount As Integer
Dim BracketBegin As Integer
Dim BracketEnd As Integer
Dim strToColour As String

For Each rngCell In Selection
    CharCount = Len(rngCell)
    BracketBegin = InStr(1, rngCell, "(")
    BracketEnd = InStr(1, rngCell, ")")
    With rngCell.Characters(BracketBegin, CharCount - BracketEnd)
        ' Pull out string to check for +/-
        strToColour = rngCell.Characters(BracketBegin, CharCount - BracketEnd).Text
        ' If negative, colour red, otherwise green
        If InStr(strToColour, "-") Then
            .Font.Color = vbRed
        Else
            .Font.Color = vbGreen
        End If
  End With
Next rngCell
End Sub

[Example][1]


  [1]: https://i.stack.imgur.com/4lyN4.png

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