VB and VBA Users Source Code: Hide cells containing errors in an Excel worksheet
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Hide cells containing errors in an Excel worksheet
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Friday, July 28, 2000
Hits:
643
Category:
Unspecified
Article:
If you don't want the cells containing errors to be visible in Excel worksheets, then paste the following code into worksheets code module: 'The code is currently set up to hide the #DIV/0! error message, but 'can easily be modified to hide other types of error message. Private Sub Worksheet_Calculate() Dim rErrorRange As Excel.Range, cErrorCell As Excel.Range 'Unhide any hidden cell values, by reseting the font color to black Me.Cells.Font.ColorIndex = 1 On Error GoTo ErrNotFound 'Get any cells containing errors Set rErrorRange = Me.Cells.SpecialCells(xlCellTypeFormulas, xlErrors) 'Loop over error range For Each cErrorCell In rErrorRange If CLng(cErrorCell.Value) = 2007 Then 'Div/0 Error 'Set font color to white cErrorCell.Font.ColorIndex = 2 End If Next ErrNotFound: 'No error cells found End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
