Excel 2007 : Changing text colour within a strings

**Pigmy** · 10-21-2009, 05:22 AM

Hi

I want to create a project plan in Excel. For the progress bar, I am using the Webdings format and the characters "c" for a blank cell and "g" for an active cell however I need to change the colour of the "c" to white and the "g" to black.

I have calculated 3 text strings based on the time until the activity starts, the duration of the activity and time after that until the project ends. These strings are "cccc", "gg", "cc" which I then add together using the formula =a1&a2&a3 which gives "ccccggcc". Using Webdings this then gives me 4 blanks, 2 filled squared and 2 blanks so would show that we have 4 weeks until the activity starts and it lasts for 2 weeks.

However the "c" character shows up as an outlined box and I would like to change the format of that to font colour white. So the string would become "ccccggcc"

I've searched the web and it seems that I need to create a module to do this but that's me out of my depth!

Any ideas how I go about doing that?

Thanks

**DonkeyOte** · 10-21-2009, 06:15 AM

I confess I'm a tad confused... are you saying you want to remove the inner border(s) or all borders ? I would have thought that the borders would actually help visually to identify the no. of weeks involved.

As a general rule - a cell containing a formula can contain only one format so yes VBA would be required to a) calculate the concatenated string and b) subsequently loop the chars and format accordingly.
The "event" you would choose to use to do this would in part depend on how a1,a2,a3 are themselves populated - ie use Calculate (formulae) or Change (manual) event.
An example of Calculate method below:

Private Sub Worksheet_Calculate()
Dim lngChar As Long, vData As Variant
vData = Application.Transpose(Range("A1:A3").Value)
With Range("B1")
    .Value = Join(vData, "")
    For lngChar = 1 To Len(.Value)
        .Characters(lngChar, 1).Font.ColorIndex = 1 - (Mid(.Value, lngChar, 1) = "c")
    Next lngChar
End With
End Sub

to activate the above right click on Tab against which the code is to be applied, select View Code and paste above into resulting window ensuring macros are enabled thereafter.

the above will ensure that whenever the sheet is calculated the value of B1 is updated to reflect current A1:A3 values and is formatted accordingly on a char by char basis.

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