Sub Routine not working inside Sheet

[kwesmc]

I have as simple module which changes the font based on the name I select inside my worksheet. The code works well when I'm in the VBE and press F5, but when I'm in the worksheet and select a choice in the cell like "YES", the font reverts back to the default font. I know I'm doing something stupid with my code, but can't figure out what. Can someone help please? See code below.
Thanks so much!
Ken Mc

Sub test()
Dim cell As Object

For Each cell In Range("B2:M35")
If UCase(cell.Value) Like "*YES*" Or UCase(cell.Value) Like "*MODERATE*" Then
cell.Font.Name = "Comic Sans MS"
cell.Font.Bold = True
End If

Next cell

End Sub

 

[Norie]

Ken

How are you executing the code from the sheet?

 

[kwesmc]

I can create a button, but what I was wondering if there was anyway to make the code change the font after input into a cell?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("B2:M35")) Is Nothing Then Exit Sub
Application.EnableEvents = False
   If UCase(Target.Value) Like "*YES*" Or UCase(Target.Value) Like "*MODERATE*" Then
      Target.Font.Name = "Comic Sans MS"
      Target.Font.bold = True
   End If
Application.EnableEvents = True

End Sub

This needs to go in the relevant sheet module

 

[kwesmc]

Thank you very much Fluff!! Works great.
You are a champ!!

Thank you very much!
Ken Mc

 

[Fluff]

Glad we could help & thanks for the feedback