Bdtyktl

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?

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)
   

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

Insert line over text wanting to be highlighted, make the line as wide as you need to high light, change text color to yellow (or any color you choose) and reduce opacity of line to your liking.