VBA Code to Change Font based on Contents of Cell

[PaulFerris]

Hi all,

Sorry to ask but I have no experience with VBA, and hoping someone can help.

I have a workbook, and on the first ten sheets I need to check the contents of cells P17:P52, and based on their contents have the font for the cells changed.

If ü then the font would be wingdings at 22pt
If û then the font would be wingdings at 22pt
Else use Calibri

How do I go about making this happen?

Thanks

 

[JLGWhiz]

I assume you know how to run the code.

Sub t()
Dim sh As Worksheet, c As Range
Set sh = ActiveSheet
    With sh
        For Each c In .Range("P17:P52")
            If c.Value = Chr(251) Or c.Value = Chr(252) Then
                c.Font.Size = 22
                c.Font.Name = "Wingdings"
            End If
        Next
    End With
End Sub

 

[Ken Cowen]

Something like this should do it. You will need to edit the range. Also, the first 10 sheets may not be sheets 1 through 10 so you may need to change the way the "first 10 sheets" are identified. Also, if any of the cells are blank, or otherwise don't have an ascii character, then you will get an error. You will need some error checking or maybe just on error resume next at the beginning if you potentially have blanks in your range to be formatted.

Sub test()


Dim R As Range
Dim n As Integer, i As Integer
Dim sh As Worksheet


For i = 1 To 10


Set sh = Sheets(i)
Set R = sh.Range("a1:a8")

For n = 1 To R.Rows.Count

Select Case Asc(R(n, 1).Value)

Case 251
With R(n, 1).Font
.Name = "Wingdings 2"
.Size = 22
End With

Case 252
With R(n, 1).Font
.Name = "Wingdings 2"
.Size = 22
End With

Case Else
With R(n, 1).Font
.Name = "Calibri"
.Size = 11
End With

End Select

Next n


Next i


End Sub

I hope this helps.

Ken

 

[PaulFerris]

I assume you know how to run the code.

Thanks for this - works perfectly.

So I know how to run it manually, but how can I run it automatically say when the sheet is active

 

[My Aswer Is This]

If you want the script to run automatically every time you activate any sheet in your workbook do this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu

On the left side of the window double click on "ThisWorkbook"
And paste in the below code.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim c As Range
        For Each c In Range("P17:P52")
            If c.Value = Chr(251) Or c.Value = Chr(252) Then
                c.Font.Size = 22
                c.Font.Name = "Wingdings"
            End If
        Next
End Sub

 

[PaulFerris]

Perfect thanks

 

[My Aswer Is This]

Glad me and JLGWhiz were able to help you you.

Perfect thanks