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 ?

<pre>
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
</pre>

 

[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

<pre>
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
</pre>

 

[DaKen]

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

Doug