We help IT Professionals succeed at work.

Change Font Type based on cell value

Cynthia Hill
Cynthia Hill asked
on
I am trying to come up with some VBA code to change the font type of a cell (3 cells actually), based on the value of a cell on a separate spreadsheet.  

I've researched some of the past solutions to get a starting point, but I've gotten as far as I can on my own.  Here is the code I have so far... (see below)

It doesn't seem to like the section starting with "Sheets("Supporting Detail").Range("I46:I48").Cell.Font

Any thoughts???
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Sheets("Drop-Down Selections").Range("B124").Value = "HC" Then
Sheets("Supporting Detail").Range("I46:I48").Cell.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 8
.Superscript = False
Else
Sheets("Supporting Detail").Range("I46:I48").Cell.Font
.Name = "Wingdings3"
.FontStyle = "Regular"
.Size = 9
.Superscript = False
End If
Application.ScreenUpdating = True
End Sub

Open in new window

Comment
Watch Question

Top Expert 2008

Commented:
Have you considered using conditional formatting?

Thomas
Analyst Assistant
CERTIFIED EXPERT
Commented:
You are missing  Witha and there is no such property as Cell
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Sheets("Drop-Down Selections").Range("B124").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

Open in new window

Cynthia HillLead Consultant

Author

Commented:
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

Open in new window

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
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?
Cynthia HillLead Consultant

Author

Commented:
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

Open in new window

Top Expert 2008

Commented:
Have you considered using conditional formatting?

Thomas
Cynthia HillLead Consultant

Author

Commented:
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.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.