font type conditional formatting based on if

[Guest]

I have formulas in several non-contiguous cells to return characters. I need the font of the character and the cell fill color to change based on the character. I used the following change event for another worksheet. It works well as the characters are typed or the results of a formula from the same sheet. But is not working for this application. Conditional formatting is working for the fill color, but is it more efficient to incorporate it into this macro? Can the change macro be adapted to use the "if" results or would you assist with a new regular macro?

This could be solved so easily if font type was an option in conditional formatting.

The cells that I need changed are B16, B58, T4, T15,T34,T50.
c=light green fill, Wingdings 3 font
ê=yellow fill, Webdings font
y=red fill, Webdings font

sample formula
=IF(anotherworkbookdata!$W$158<=0.6,"y",IF(anotherworkbookdata!$W$158<=0.8,"ê","c"))


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B16:B16")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "c" Then
Application.EnableEvents = False
Target.Font.Name = "Wingdings 3"
Application.EnableEvents = True
Else
Application.EnableEvents = False
Target.Font.Name = "Webdings"
Application.EnableEvents = True
End If
End Sub

Thanks so much for your assistance.
Jodi

 

[Frrank Kabel]

Hi
if you want to use a macro you have to use the
worksheet_claculate event for this. Within this event loop
through your range, check the charatcers and change them
accordingly

-----Original Message-----
I have formulas in several non-contiguous cells to return

characters. I need the font of the character and the cell
fill color to change based on the character. I used the
following change event for another worksheet. It works
well as the characters are typed or the results of a
formula from the same sheet. But is not working for this
application. Conditional formatting is working for the
fill color, but is it more efficient to incorporate it
into this macro? Can the change macro be adapted to use
the "if" results or would you assist with a new regular
macro?

This could be solved so easily if font type was an option in conditional formatting.

The cells that I need changed are B16, B58, T4, T15,T34,T50.
c=light green fill, Wingdings 3 font
ê=yellow fill, Webdings font
y=red fill, Webdings font

sample formula
=IF(anotherworkbookdata!$W$158<=0.6,"y",IF (anotherworkbookdata!$W$158<=0.8,"ê","c"))


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B16:B16")) Is Nothing Or

Target.Cells.Count > 1 Then Exit Sub

 

[Frank Kabel]

Hi Jodi
so your probelm is solved? Or do you need further assistance?. If yes
you may post your existing macro

 

[Guest]

This code works, but it has to be run. I've tied the macro to a autoshape-push to run. I would like a macro that runs automatically, as the result of a conditional function (if). It would need to be on all sheets in the workbook.

Thanks for any modifications you can provide.

Sub ChangeFontsForProperSymbolDisplay()

Dim Inrange As Range
Dim rng As Range

'Set the Inrange value
Set Inrange = Range("B16,B58,T4,T15,T34,T50")

'Start the loop based on cell value
For Each rng In Inrange.Cells
If rng.Value = "c" Then
'Change the font to get the arrow symbol.
rng.Font.Name = "Wingdings 3"
Else
'Leave the font as is.
rng.Font.Name = "Webdings"
End If
Next rng
'next range not used