Change Font Type based on cell value

Have you considered using conditional formatting?

Thomas

Thanks for the quick responce.

I tried the code and the Worksheet Change function doesn't seem to be doing what I had hoped(?).  The code above changes the font to Calibri, but no matter what other values appear in ("Drop-Down Selections").Range("B124")...it never changes to wingdings3.

I thought perhaps the fact that it's on a separate sheet might be causing a problem, so I added a formula to a cell on the Supporting detail tab that references the cell B124 from the other tab, but it's still not changing the font when something beside HC is selected.  

Should I be using another function in place of the Worksheet_Change function?  

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Sheets("Supporting Detail").Range("K46").Value = "HC" Then
        With Sheets("Supporting Detail").Range("I46:I48").Font
            .Name = "Calibri"
            .FontStyle = "Regular"
            .Size = 8
            .Superscript = False
        End With
    Else
        With Sheets("Supporting Detail").Range("I46:I48").Font
            .Name = "Wingdings3"
            .FontStyle = "Regular"
            .Size = 9
            .Superscript = False
        End With
    End If
    Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Does the code actually get triggered when you want/expect it to?

You can test for that by adding a breakpoint to it (F9) and then doing whatever it is you want to trigger it.

eg change the value in the cell(s) you want to monitor.

Which sheet's module is the code actually in?

No...it was not executing when needed consistenly.  

I had to add in an activeX text box, and use the Textbox change function instead of the Worksheet change function to get it to work.  

I've not really used the Worksheet Change function in the past, but it might not be working because the cell that is being evaluated for changes is a cell with a formula (no one is clicking into and actually changing the value of the cell(?).  

Unless you have additional words of wisdom on the Worksheet change function...I will plan to go with the textbox change function, and just keep the text box out of sight.  

Thanks again for your help!  

Public Sub TextBox77_Change()
    Application.ScreenUpdating = False
      If Sheets("Drop-Down Selections").Range("B124").Value = "HC" Then
        With Sheets("Supporting Detail").Range("I46:I48").Font
           .Name = "Arial Narrow"
            .FontStyle = "Regular"
            .Size = 12
            .Superscript = False
        End With
       Else
        With Sheets("Supporting Detail").Range("I46:I48").Font
           .Name = "Wingdings"
            .FontStyle = "Regular"
            .Size = 12
            .Superscript = False
         End With
    End If
       Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Have you considered using conditional formatting?

Thomas

Conditional formatting will not allow you to chage the font type (e.g. from Arial to Wingdings), it only allows you to do things like chnage color, bold, etc.

If it was a formula being changed then yes that wouldn't trigger the Change event.

You could try using the Calculate event but it might be better to stick with the textbox.