Auto Size Font, VBA?

[BWMagee]

Hi, I have a spreadsheet with multiple formulas that return #DIV/0!

For the working of my spreadsheet this is fine, as data might be input later that will then yield a value. However they distract from the appearance of the spreadsheet, so I'm wondering if there is VBA code that can say something like:

For Spreadsheet Named "Sheet1"
Font Size of "#DIV/0!" to 6pt.

Many thanks!

 

[My Aswer Is This]

In what range in the worksheet should we look for this?

 

[Fluff]

Rather than VBA how about conditional format?
Whilst you cant change the font size, you could make the font white (It "disappears"), or maybe a light grey

 

[My Aswer Is This]

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

Not sure if this may slow down your sheet performance.
Font size changes from 12 to 6 depending on result.

Private Sub Worksheet_Calculate()
Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If c.Text = "#DIV/0!" Then
            c.Font.Size = 6
        Else
            c.Font.Size = 12
    End If
    Next
End Sub

 

[BWMagee]

Rather than VBA how about conditional format?
Whilst you cant change the font size, you could make the font white (It "disappears"), or maybe a light grey

Good idea. Conditional format won't let me select #DIV/0! only (has to apply to all Errors), but will make the text light grey so I can still see the other errors when they display. Thanks!

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

Not sure if this may slow down your sheet performance.

Unfortunately it slowed down my sheet performance to the point it wouldn't respond (it's a large sheet). Nonetheless, thanks for the response!

 

[Fluff]

Good idea. Conditional format won't let me select #DIV/0! only (has to apply to all Errors), but will make the text light grey so I can still see the other errors when they display. Thanks!

Yes it will, use a formula to determine which cells & then use this
=ERROR.TYPE(A1)=2

 

[My Aswer Is This]

Since you did not say what range to look in my script looked in the entire sheet.
We maybe could use cell change but would need to know the range and what causes the cell change.