Macro code to partially format font colour of result in cellcontaining a formula

[Mike Magill]

Hi,

I am trying to conditionally format the colour of the text in a cell
but I have too many conditions to be able to use the Conditional
Formatting function.

I have written the following code. It works perfectly on cells with
actual text in but doesn't change the font colour where the cell
contains a formula that produces a text result.

The result will always be a two character string and I want to colour
the first character one colour and the second character another
colour.

Any help would be greatly appreciated.

Thanks


Private Sub Worksheet_Change(ByVal Target As Range)

For Each C In Range("G12:BX64")

If (C.Value <> "") Then

On Error Resume Next

Select Case Left(C, 1)

Case "R"

With C.Characters(Start:=1, Length:=1).Font
.ColorIndex = 3
End With
With C.Characters(Start:=2, Length:=1).Font
.ColorIndex = 1
End With

With C
.Interior.ColorIndex = 3
End With

Case "A"

With C.Characters(Start:=1, Length:=1).Font
.ColorIndex = 45
End With
With C.Characters(Start:=2, Length:=1).Font
.ColorIndex = 1
End With

With C
.Interior.ColorIndex = 45
End With


Case "G"

With C.Characters(Start:=1, Length:=1).Font
.ColorIndex = 4
End With
With C.Characters(Start:=2, Length:=1).Font
.ColorIndex = 1
End With

With C
.Interior.ColorIndex = 4
End With

Case Else

With C.Characters(Start:=1, Length:=1).Font
.ColorIndex = 1
End With

With C
.Interior.ColorIndex = 0
End With


End Select

Select Case C

Case "RR"

With C.Characters(Start:=2, Length:=1).Font
.ColorIndex = 3
End With

Case "AA"

With C.Characters(Start:=2, Length:=1).Font
.ColorIndex = 45
End With

Case "GG"

With C.Characters(Start:=2, Length:=1).Font
.ColorIndex = 4
End With

End Select
End If

Next C
End Sub

 

[Ron Rosenfeld]

I have written the following code. It works perfectly on cells with
actual text in but doesn't change the font colour where the cell
contains a formula that produces a text result.

Excel does not allow you to have more than one format within the cell UNLESS
the contents is a text string.

You will need to convert the contents to a text string

(e.g. cell.value = cell.text)

before you can do what you describe.

One alternative might be to have the Macro also execute the formula, and then
write the results to the cell as a text string.

And you could consider doing this as an on event macro so as to automate the
whole thing.
--ron

 

[Mike Magill]

Excel does not allow you to have more than one format within the cell UNLESS
the contents is a text string.

You will need to convert the contents to a text string

        (e.g. cell.value = cell.text)

before you can do what you describe.

One alternative might be to have the Macro also execute the formula, and then
write the results to the cell as a text string.

And you could consider doing this as an on event macro so as to automate the
whole thing.
--ron

Thanks for the swift reply.

Converting the cell value to text wouldn't be ideal as the cell values
are not static and will change over time. Ill try to execute the
formula from within the Macro but I've never done this before. I'll
search the web for some guidance.

Also, not sure I've used event macros before but, again will try.

Thanks again

 

[Ron Rosenfeld]

Thanks for the swift reply.

Converting the cell value to text wouldn't be ideal as the cell values
are not static and will change over time. Ill try to execute the
formula from within the Macro but I've never done this before. I'll
search the web for some guidance.

Also, not sure I've used event macros before but, again will try.

Thanks again

You're welcome.

Glad to help.

Take a look here http://www.contextures.com/xlfaqMac.html and check for Running
a Macro automatically.

Post back if you need more suggestion.
--ron