Today I want to share with you a very useful Excel VBA macro. This is a small bit of code that come in useful to easily highlight all cells in your Excel worksheet that have data validation.
I have found this macro very useful if I have inherited an Excel workbook. It is also a regular if I have to troubleshoot on an existing Excel solution.
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.
Before we begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook.
What Is The Difference In These Locations?.
Well, if you save the code in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, I may want to reuse the code so I will store it in my Personal Macro Workbook. When you select this option then Excel creates (if it is not already created) this workbook and saves the macro in that location, by default this macro workbook is named Personal.xlsb. This is a very useful macro. It is very specific to this workbook process but I do not want to save the workbook as a macro extension. I therefore still want to save it in my personal macro workbook.
If you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
I want to use this code over and over again so I will choose at this time to save it in my Personal Macro Workbook.
Preparing To Write The Code.
First, I need to start off the process by inserting a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro.
In this example, I have called it CellsWithValidation . Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code to highlight the cells with data validation between these two lines of code.
[stextbox id=’info’]
Sub CellsWithValidation
End Sub [/stextbox]
Use The Special Cells Method.
The next line of code is really simple. I use the Special Cells method as it returns a Range Object that represents only those type of cells we specify.
In this example macro the cells type is those that contain any type of data validation. This is a very efficient way to return a range which then you can do carry out another process. So, this line of code selects or returns all cells that contain validation. A very easy way to highlight cells with validation.
[stextbox id=’info’]
Selection.SpecialCells(xlCellTypeAllValidation).Select
[/stextbox]
Highlight The Cells With Validation.
Now the range of cells with validation is selected we can highlight them for easy identification. In this example, I will highlight the range of cells that are subject to validation in a note form.
[stextbox id=’info’]
Selection.Style = “Note”
[/stextbox]
Ending the Macro.
Finally, the code ends once the file save has been completed with the “End Sub" piece of code. This was already entered into the module when the name of the macro was set. How cool?.
[stextbox id=’info’]
End Sub
[/stextbox]
If you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Blog Posts.