Hello Excellers, I want to share with you a handy Macro that I use regualrly that I have attached to a psuh button so I can easily access it. The Macro, with the click of a button will identify and flag (by formatting the cell) any cells in my workbook that contain formulas.
If you want to download the example workbook that is associated with this post you can do so here.
This Macro pretty much carries out the same steps as the GoTo Special Dialog box, which you need to manually select they type of cells to find based on some pre defined definition such as Blanks, Formulas, errors etc.
Let’s write a macro that programmatically does the same thing for the whole workbook, with one click.
We will be using the Special Cells Method which requires a parameter type that represents which the type of special cell we are looking for. In our case for this macro it is xlCellTypeFormulas. – A special range of cells cells that only contain formulas with the following syntax
expression is a required field which has to be a Range Object for Example Range(A1:A2), ActiveSheet.UsedRange or in our example below, it refers to ActiveWorkbook.Worksheets.
Here are the Macro details.
Our Macro is called ‘Macrocolourformulas’
Let’s look at the stages of the code.
Step 1. We declare are variables
Dim ws As Worksheet
Step 2. We Avoid errors if there are no formulas found in the worksheets
On Error Resume Next
Step 3. We begin to loop through all of our worksheets
For Each ws In ActiveWorkbook.Worksheets
Step 4.Select all cells with formulas and highlight them
.Font.Color = -16776961
Step 4. Go to the next worksheet
This will change the font colour of any cells that contain formulas to RED. Give it a go. They really stand out.
I would store this in my Personal Macro Workbook, as in this was it is always available for any of my workbooks to use.
Other Blog Posts You May Be Interested In-
Macro Mondays – Moving And Undocking The Immediate Window In The Visual Basic Editor
Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.