Auto Format Excel Cells with Error Value

With the simple Excel macro below, we can make all cells in active Worksheet that contains Error value in it, like #NULL, #Div/0!, #VALUE!, #Ref, #NAME?, #NUM!, And #N/A, will automatically having cell format that different/stand out among all other cells.

In this example, background color of the cell that contains the error value will automatically change color to red, each time the Excel Worksheet activated.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim errCells As Range

Set errCells = Cells.SpecialCells(xlCellTypeFormulas, xlErrors)
errCells.Interior.Color = 255

Set errCells = Nothing
End Sub

Note that using the Cells.SpeciallCells function, we do not need to perform any looping to get Cells which contains the Error value.

This is just like automating Excel cell conditional formating process. Instead of selecting manually all the cells contains Error value or select all cells and then performing cell conditional format, with above code, we do it automatically using simple Excel VBA macro.

Related Entries

  • Select All Cells With Formula In It

    Let say we want to distinguish between ordinary cells and cells with formula in it, so we give a special background color to the cells with formula. For doing it manually is time consuming, especially on Excel Worksheet with large of cells with formula in it (usually a very complex worksheet). The following vba macro code will...

  • Finding Cell with Minimum/Maximum Value in Active Worksheet

    Let say we want to find position of cell containing the minimum/maximum value in current/active Excel worksheet, and then after we found the cell, we will change the cell format to make it stand out before other cells. The logic is simple, we just need to use Excel MIN function to find the minimum/maximum value on...

  • How To Hide an Excel Worksheet

    Some time we want to hide a certain Excel Worksheet from view, and it’s a common practice to use select the Worksheet, go to menu Format > Sheet > Hide. Using the method explained above is right, but unfortunately, others people can easily unhide the Worksheet using the same method, only this time, instead of selecting...

  • Excel VBA Looping Tutorial

    Do while … loop, do until … loop, do … loop while, do … loop until, and while … wend are the format of looping that we can use in Excel VBA. The main reason why we need to perform looping is to simplified a repeating tasks. Rather then manually calling a block of code over...

  • How To Find the Last Row That Contain Data in Excel?

    UPDATE June 13, 2008: Another alternative way to find the last row with data :Function LastRow() As LongDim ix As Long ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ixEnd FunctionI did a simple test, and the function will return the correct last row position...

Valuable Resources

If you found this page useful, please consider bookmark it using social media or add a link to this page.

  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • connotea
  • Diigo
  • email
  • Fark
  • Identi.ca
  • Live
  • MisterWong
  • MySpace
  • Netvibes
  • NewsVine
  • PDF
  • Ping.fm
  • Propeller
  • Reddit
  • Simpy
  • StumbleUpon
  • Technorati
  • Twitter
  • Wikio
  • Yahoo! Bookmarks

Incoming search engine terms: Display or hide all zero values on EXCEL worksheet BY USING FIND AND REPLACE, why does excel sum say max, how to deal with #N/A values excel macros, how to automatically set the format of the column using macro, how a cell value will auto copy by loop function, hide all columns containing errors, hide #value errors in excel, hide #value error in excel, format excel cells containing error values, format change in excel macro error, format all rows excel vba for loop, format all charts macro, find error and condition formating excel, how to know if cell #DIV/0! vba, how to use macros to change div by 0 entire worksheet

«
»