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

Related Entries

  • 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...

  • 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...

  • Number of Cells/Rows/Columns With Formula

    A few weeks ago I received this question from someone called LS: Hi I want to write a macro that counts the following - Number of cells with formula - Number of Rows with formula - Number of Columns with formula Please let me know if there is a way of doing it in Excel VBA To find out the number of...

  • Move Cursor To One Cell Below Last Row With Data

    Ever want to paste some data into new empty cell after the last cell with data in certain column? Then take a look at this simple macro.'first, select cell in the first row of that column, like A1, K1, etc Range("A1").Select'move to the last...

  • 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: vba select all cells, select all macro, vba select all cells with data, position of select all button in excel, search and select a cell with text macro, macros for excel to highlight all text within a cell, select all cells with formulas, Select All Excel VBA, select all find all excel, changing all formulas in all cells in excel 2007, select cells with formulas, selecting all data in excel macro, selecting cells without formulas vba, vba find formulas with errors, vba select all

«
»