How to change the font size based on value of the cell?

Hello,

I would like to change the font size of cell C4 to 22, if the content of cell C4 is numeric and 16 if the content is a text. I have written a macro to do this; however, I would like to make it automatically change any time the content of cell C4 changes. Currently, I have a button to click to run the macro. The VBA checks cell C4 and changes the font size accordingly. How can I program the macro to recognize when cell C4 changes and apply these conditions automatically? Is there a way to do it without VBA? I do not think that conditional formatting will work.

Sub Button3_Click()

     If IsNumeric(Worksheets("Sheet1").Cells(4, 3)) Or Worksheets("Sheet1").Cells(4, 3) = "" Then
        Worksheets("Sheet1").Range("C4").Font.Size = 22
    Else
        Worksheets("Sheet1").Range("C4").Font.Size = 16
    End If
    
End Sub

Thank you for your help!

redsab

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) = "C4" Then
    If IsNumeric(Target.Value) Or Target.Value = "" Then
        Target.Font.Size = 22
    Else
        Target.Font.Size = 16
    End If
End If

End Sub

Sbarro79, Thank you for your input. Your code worked perfectly! However, when I tried to add the code to my real world project, I could not get it to work. Could you taking a look at it and see if you can find out my problem. I have included my spread sheet.

Text value:
Go to worksheet Sheet1 and choose Product1 from the drop down list in cell D9. Go to worksheet PA_Running_Sheet_A. In cell D11 you should see the text "2,359/2,302" without the quotes. The value of this cell is a text value. Therefore, it should reduce the font size to 16. But the font size remains at 22. I enter the code nearly identical the way you gave it to me.

Numberic value:
Go to worksheet Sheet1 and choose Product3 from the drop down list in cell D9. Go to worksheet PA_Running_Sheet_A. In cell D11 you should see the number 9,204. The value of this cell is a numeric value. Therefore, it should increase the font size to 22.

In the VBA editor I entered the Worksheet_Change subroutine in the PA_Running_Sheet_A worksheet area. Is that correct? Thank you so much for your help!

redsab

Hi redsab

I looked into your file and I think I know what's the problem - cell D11 value is modified by formula (which I didn't know about initially) and that's why the code doesn't work. Worksheet_Change event is triggered when cell is changed manually, or by VBA code.
Meanwhile I tried to do something with this and here it is (add this code to worksheet Sheet1 module)

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "D9" Then
With Worksheets("PA_Running_Sheet_A").Range("D11")
    If IsNumeric(.Value) Or .Value = "" Then
              .Font.Size = 22
              .Offset(0, 1).Font.Size = 22
         Else
            .Font.Size = 16
            .Offset(0, 1).Font.Size = 16
        End If
        End With
        Worksheets("PA_Running_Sheet_A").Activate
End If
End Sub

I hope this is, what you're looking for

Sbarro79,
Yes! That did the trick! Thanks for all your help. I know that may have seemed a little petty, but I am all about detail. Your the best!

redsab