Changing font style and color using VBA

[Spongie]

I have been trying (unsuccessfully!) to write some code to change the font
style and color based on specific criteria.

I have a spreadsheet with input cells in columns A & B and a formula in
column C to determine the % variance between the 2. What I want to do is in
column D return a symbol based on a RAG status (Red, Amber, Green). I can't
do this using a formula as the Red and Green symbols are Wingdings and the
Amber symbol uses Wingdings 3.

So if A1=40 and B1=45 the variance is 11% (as shown in C1)and I'd like this
to return a particular symbol (Amber triangle), which I believe is:
Value = "Â"
Font Name = Wingdings 3
Font Color = RGB(255,192,0)

But I'm struggling with the actual code....please don't laugh at my pathetic
attempts (completely self-taught) but this is what I wrote....

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("G6") <= 0.1 Then
With Range("H6")
..Value = "l"
..Font.Name = "Wingdings"
..Font.Color = RGB(0, 0, 255)
End With

ElseIf Range("G6") <= 0.2 Then
With Range("H6")
..Value = "Â"
..Font.Name = "Wingdings 3"
..Font.Color = RGB(255, 192, 0)
End With

ElseIf Range("G6") > 0.2 Then
With Range("H6")
..Value = "«"
..Font.Name = "Wingdings"
..Font.Color = RGB(255, 0, 0)
End With

End If

It inputs the correct symbol but then Excel completely freezes...what am I
doing wrong??

Any help greatly appreciated!

 

[Luke M]

Since your Change Event macro "changes" the sheet, you need to temporarily
disable events to prevent an endless loop. Note that the EnableEvents does
not automatically get reset to "true" at the end of a macro, so you want to
make sure that is always included at the end.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
With Range("H6")
If Range("G6") <= 0.1 Then
..Value = "l"
..Font.Name = "Wingdings"
..Font.Color = RGB(0, 0, 255)

ElseIf Range("G6") <= 0.2 Then
..Value = "Â"
..Font.Name = "Wingdings 3"
..Font.Color = RGB(255, 192, 0)

ElseIf Range("G6") > 0.2 Then
..Value = "«"
..Font.Name = "Wingdings"
..Font.Color = RGB(255, 0, 0)

End If
End With
Application.EnableEvents = True
End Sub

 

[Spongie]

You are an absolute star - thank you very much!!!

 

[Gord Dibben]

Luke

Just an add-on to your advice about disabling/enabling events.

In order to reset events in case of an error you should trap for an error.

On Error Goto stoppit
Application.EnableEvents = False

code to run

stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP