Changing colour of half a cell's text

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
Folks,

I have a concatentated formula that appears in the cell as such:

12/34 e.g. =formula&"/"&formula

I would like to change the colour of the second formula (34 in the example) to Red for instance.

I have experimented with the Custom Format function with no luck, but that is likely due to my lack of familiarity with how it works

I also tried the TEXT function, inserting "[Red]0" but that not suprisingly didn't change the colour.

Is there a way to do this using Custom Format?

I'm open to a VBA solution but would prefer not

Cheers
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
*bump*

Well, it's the end of the day in my part of the world, perhaps the night shift might know of an answer ...
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
The only way to do this is by VBA, BUT it would mean destroying the formula ( you can only have mixed font colours on fixed text, not formulae ). Would this route be acceptable to you?
 

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
Thanks Glenn

VBA would be acceptable - as a manual workaround I copied the worksheet, pasted the cells as values, formatted them as text and changed them all manually.

Some VBA code which replicates the above process (I would need to keep the formulas, so a copy of the sheet is needed) would be appreciated. There's ~40 cells to be updated so it's an annoying process.

The cells are grouped together which from a range point of view should help
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

Here is a starting point:
Code:
Sub ChangeFontPartial()
    Dim blSlash As Boolean, c As Range
    For Each c In Range("b2:b5")
        blSlash = False
        For i = 1 To c.Characters.Count
            If c.Characters(i, 1).Text = "/" Then
                blSlash = True
            ElseIf blSlash Then
                c.Characters(i, 1).Font.ColorIndex = 3
            End If
        Next
    Next
End Sub
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
The only way to do this is by VBA, BUT it would mean destroying the formula (you can only have mixed font colours on fixed text, not formulae ).

The only other way to do this is without VBA, by copying the cell, pasting it on itself using Paste Special - Values, then editing the cell directly. You can select a portion of the cell's text and apply specific colors or other text formatting.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547

ADVERTISEMENT

- as a manual workaround I copied the worksheet, pasted the cells as values, formatted them as text and changed them all manually.

Yes Jon, that's right ... see the quote above ( from JohnnyTightlips ).
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
I thought that was the case. It's not like you to say something unnecessarily. Love your site by the way.
 

Forum statistics

Threads
1,141,587
Messages
5,707,258
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top