Posts Tagged ‘excel special cells’

Auto Format Excel Cells with Error Value

Posted on the June 3rd, 2009 under Cells and Range by Poer @ Excel VBA/Macro

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.

Select All Cells With Formula In It

Posted on the October 25th, 2008 under Cells and Range by Poer @ Excel VBA/Macro

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 do the trick for us.

Sub SelectAllCellsWithFormula()

    'select all cell with formula
    Cells.SpecialCells(xlCellTypeFormulas).Select

    'change cell background color
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

End Sub

FIN