Changing colour of half a cell's text

[JohnnyTightlips]

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

 

[JohnnyTightlips]

*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]

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]

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]

Here is a starting point:

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]

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]

- 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 ).

 

[Jon Peltier]

Sorry, I hadn't seen his comment.

 

[GlennUK]

I thought that was the case. It's not like you to say something unnecessarily. Love your site by the way.

 

[JohnnyTightlips]

Glenn, Thanks - worked a treat