Excel 2007 : Changing text colour within a strings

**Pigmy** · 10-21-2009, 07:19 AM

Hi,

I've attached a copy of the plan - sometimes its easier to see these things in the flesh than try to describe them in words!

I tried manipulating the code you posted however I could only get it to change the colour of the whole text strings rather than changing bits of it.

Hope this helps

Alan

**DonkeyOte** · 10-21-2009, 07:31 AM

Alan, based on your file the below should work (?) - replacing existing Calculate event in full

Private Sub Worksheet_Calculate()
Dim lngChar As Long, vData As Variant, rngCell As Range
On Error GoTo ExitPoint
Application.EnableEvents = False
For Each rngCell In Range("H4:H11").Cells
    vData = Application.Transpose(Application.Transpose(rngCell.Offset(, -3).Resize(, 3).Value))
    With rngCell
        .Value = Join(vData, "")
        For lngChar = 1 To Len(.Value)
            .Characters(lngChar, 1).Font.ColorIndex = 1 - (Mid(.Value, lngChar, 1) = "c")
        Next lngChar
    End With
Next rngCell
ExitPoint:
Application.EnableEvents = True
End Sub

That said would it not be easier to simply use conditional formatting ?
ie populate I onwards with c/g based on B:D and format both fill & font accordingly ? This may slow your file a tad but would be simpler.

**Pigmy** · 10-21-2009, 07:41 AM

Many thanks for that - it works a treat.

I have used the conditional formatting method before but sometimes its a bit clunky. You end up typing in a new date in one column and forgetting to update the bar chart side and then all confusion breaks out as to the actual date.

Thank you

Alan