Conditional font size

[DaKen]

Is it possible to change font size in a cell based on a criteria. I'm trying to make cells that contain a formula appear in a larger font size.

Thanks!!!
Doug

[Tom Urtis]

Right click on your sheet tab, left click on View Code, and paste this in. Modify for font size. The error trap is for the occasions when no formulas exist on the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Cells.SpecialCells(xlCellTypeFormulas, 23).Font.Size = 24
End Sub

Tom Urtis

[Juan Pablo González]

I think you would have to "reset" all cells font size, before changing the cells with formulas, like this

Cells.Font.Size = 12

and then do what Tom suggested:

Cells.SpecialCells(xlCellTypeFormulas, 23).Font.Size = 24

[DaKen]

Can you assign a range for the font size of 24 to take affect?

[Juan Pablo González]

Do you mean like this ?

Private Sub Worksheet_Change(ByVal Target As Range) 

Dim Rng as Range

Set Rng = Range("A1:B20")

If Not Intersect(Target,Rng) is Nothing then

    On Error Resume Next 

    Rng.Font.Size = 12

    Rng.SpecialCells(xlCellTypeFormulas, 23).Font.Size = 24 

End If

End Sub 

[DaKen]

Perfect! If it's not too much to ask, can I also format color and bold/italic? Also center in the cell?

[ This Message was edited by: DaKen on 2002-02-21 06:00 ]

[Juan Pablo González]

You can use the macro recorder to get the appropiate commands

Private Sub Worksheet_Change(ByVal Target As Range) 

Dim Rng as Range

Set Rng = Range("A1:B20")

If Not Intersect(Target,Rng) is Nothing then

    On Error Resume Next 

    With Rng.Font

      .Size = 12

      .Bold = False

      .ColorIndex = -4105

    End With

    Rng.HorizontalAlignment = xlGeneral

    With Rng.SpecialCells(xlCellTypeFormulas, 23).Font

      .Size = 24 

      .Bold = True

      .ColorIndex = 3

    End With

    Rng.SpecialCells(xlCellTypeFormulas, 23).HorizontalAlignment = xlCenter

End If

End Sub 

[DaKen]

Thanks again for your patience and help. I really appreciate the opportunity to access this type of forum!

Doug