Hello Excellers, I want to share with you a handy Macro that I use regularly. Do you ever have a worksheet where you need to format every cell that contains a formula? It can be a tedious task if you have to do it one by one. But there is an easy way to do it quickly and easily using Excel macros. In this blog post, I’ll show you how to create a macro that will select and format all cells that contain a formula. In addition, 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. So let’s get started!
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.
So, let’s write a macro that programmatically does the same thing for the whole workbook, with one click.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you cannot see the Developer Tab, then you may need to enable it. Feel free to watch my YouTube video on enabling the Developer Tab. When you decide to write an Excel macro, you first need to choose where to store it. You have two choices.
- Store it in the current Excel workbook you are working on creating.
- Save the macro in your Personal Macro workbook.
So, what is the difference? It is simple. If the code you are writing is specific to that workbook, then store it within the workbook. If you can reuse a type of macro, keep it in your Personal Macro Workbook.
In this example, I could reuse this macro over and over again. So, it makes sense to store it in my Personal Macro Workbook. We need to start the process by inserting a new module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Next, type Sub, then the name of your macro. In this example, I have called the macro
The Special Cells Method.
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.SpecialCells(Type, Value)
where
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
With ws.Cells.SpecialCells(xlCellTypeFormulas)
.Font.Color = -16776961
End with
Step 4. Go to the next worksheet
Next ws
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-
