Hello, Excellers welcome to my next blog post in my #MacroMonday #Excel tip 2019 series. Today lets’ look at how to create a quick and simple Excel VBA macro which will highlight all cells that contain a value in an Excel Worksheet. I have this Macro to hand so I can quickly run it to identify cells with values only (as opposed to those that contain formulas). I find this especially useful I have received or inherited a new workbook and do not know it’s structure. Or, if I am revisiting an Excel solution I created some time ago. It really helps to get a bird’s eye view of the workbook.
Manual Solution To Highlight Value Cells.
There is a manual solution that many Excel users already use. So, here is the usual or the alternative (depending on your personal viewpoint). You can use the F5 shortcut key or the GoTo Special method.
- Select the cells or cell range you want to highlight
- Home Tab | Find & Select Group | GoTo Special
- Select Constants
You now have a few options to choose from. If you want to highlight all cells that contain just cell values irrespective of whether it is a text, number, etc then leave them all selected. If you want to highlight just number values then just select that option.
Once Excel has found all of the cells that contain constants then use either one of the methods below to color or highlight those cells.
- Right Click | Format Cells | Select Fill Tab | Select Fill Type | Hit Ok
- Home Tab | Font Group | Select Your Fill Color
Now, that is a great way to highlight cells with constants or values. But, I find it a little long winded, especially if I can write a little or tiny bit of Excel VBA code (or Macro) once. I can then just call this macro again and again when needed with a click.
Preparing To Write The 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. You then have a choice, you can either create a module to store your code either /#’];//in your Personal Macro Workbook or in your current workbook. What’s the difference?. Well, if you save the macro 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.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
I want to store this macro in my Personal Macro workbook.
Starting The Macro
We need to start off the macro by inserting a New Module. Do this by selecting the Personal.xlsbworkbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it Sub Constants. 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 between these two lines.
[stextbox id=’info’]
Sub Constants()
End Sub
[/stextbox]
Declaring A Variable
We need to declare a variable. This ensures that Excel creates a memory container for this value. In this example, we need to declare the following. This ensures the user selected range is stored in the Excel memory.
[stextbox id=’info’]
Dim rng As Range
[/stextbox]
Selecting All Cells With Constants/Values
Excel selects all of the cells with constants. We use the Special Cells Method. This returns a Range Object that represents all the cells that match the specified type and value.
[stextbox id=’info’]
Selection.SpecialCells(xlCellTypeConstants).Select
[/stextbox]
The Syntax of this Special Cells Method is
expression.SpecialCells(Type, Value)
We are using the xlCellTypeConstants. Cells containing constants. This is the type. We are also faced with selecting the value which represents the different type of cell values which we can select. A summary of the options can be seen below.
XlSpecialCellsValue
| XlSpecialCellsValue constants | Value |
|---|---|
| xlErrors | 16 |
| xlLogical | 4 |
| xlNumbers | 1 |
| xlTextValues | 2 |
By leaving the value argument out, then by default, all of the above will be selected or highlighted. If I only wanted to highlight error then I would add 16 as the value into the line of code, numbers it would be 1 and so on.
Formatting The Highlighted Cells.
Now we have the cells that have constant values highlighted or selected, we can take this to the next step and fill those cells with color.
[stextbox id=’info’]
Selection.Cells.Interior.Color = vbRed
[/stextbox]
I have chosen to fill then with the color red. Feel free to choose which color you require. A list of the color constants can be seen below.
| Constant | Value | Description |
|---|---|---|
| vbBlack | 0x0 | Black |
| vbRed | 0xFF | Red |
| vbGreen | 0xFF00 | Green |
| vbYellow | 0xFFFF | Yellow |
| vbBlue | 0xFF0000 | Blue |
| vbMagenta | 0xFF00FF | Magenta |
| vbCyan | 0xFFFF00 | Cyan |
| vbWhite | 0xFFFFFF | White |
Ending The Macro
Finally, once all of the selected range has been checked for negative values the code finally ends. This instruction was already entered into the module for us when started the type the name of the Excel
macro.
[stextbox id=’info’]
End Sub
[/stextbox]
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below
How To Excel At Excel – Macro Mondays Blog Posts.
So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.