Formula to change font

Brister · Oct 21st 2005

Hey guys,

Is there a way to change the font of a cell with a formula? What I need to do is check 20 cells in a column, and if all 20 cells are filled in I want a check mark which I can get from Font:Wingdings which is Char(252). Now if there are not text in all 20 cells, it is just going to count, and give me a number, but I have to switch fonts again. Any ideas on this? I can do the If formulas and stuff, just don't know what to put in to change Font from Arial to Wingdings or visa versa.

Thanks

chrisxs5 · Oct 21st 2005

Re: Formula to change font

Have you considered doing conditional formatting with a count fromula?

Brister · Oct 21st 2005

Re: Formula to change font

Conditional Formatting won't allow me to change Fonts

Seti · Oct 21st 2005

Re: Formula to change font

Neither will a formula. You'll need VBA for this one.

chrisxs5 · Oct 21st 2005

Re: Formula to change font

I have never noticed that before. I use conditional all the time and never saw that it didnt allow access to that.

Seti · Oct 21st 2005

Re: Formula to change font

Here is some VBA that does what you are looking for. It is not the best written, but I think it will work fine.

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim counter As Long
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
    counter = 0
    For Each cll In Range("A1:A20")
        If Len(cll) > 0 Then counter = counter + 1
    Next cll
    If counter = 20 Then
        With Range("B1")
            .Value = "ü"
            .Font.Name = "Wingdings"
        End With
    Else
        With Range("B1")
            .Value = counter
            .Font.Name = "Arial"
        End With
    End If
End If
End Sub

Display More

This code checks cells A1:A20 for non blank cells. If there are 20 cells filled in, you get a check mark, otherwise a count of the number of filled cells. This goes in B1.

This code goes in the sheet module. If you need help getting it in the right place, let us know.

Brister · Oct 21st 2005

Re: Formula to change font

Thanks Seti,

I came up with a way to do it without VBA. Using the Insert Symbol, I inserted the check mark symbol in X1. Then did my formula:
=IF(COUNTA(D17:D46)=20,X1,COUNTA(D17:D46))

This works perfectly.

Thanks for your help.

Formula to change font

Participate now!

Share