Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font style and color using VBA
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font style and color using VBA
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Spongie" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font style and color using VBA
You are an absolute star - thank you very much!!!
"Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Spongie" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font style and color using VBA
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 On Wed, 30 Sep 2009 08:15:02 -0700, Luke M wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing font style/size/color/etc for multiple series in a chart? | Charts and Charting in Excel | |||
Font color changing by itself | Excel Discussion (Misc queries) | |||
Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
Changing Font Color | Excel Discussion (Misc queries) | |||
changing font style in a complex worksheet function | Excel Worksheet Functions |