Let’s start with something simple. Assume that we have a big spreadsheet and we want to highlight it based on a certain subject — for example, expenses about car. For the sake of the example we’ll keep it short, but in real life you could be dealing with hundreds of rows.
We can see that cell B2 and B7 contain the word “car”, so how do you highlight these cells? You can do this with a VBA macro — VBA gives you full programmatic control of Excel and is far more flexible than a single cell formula.
So let’s start programming!
1. Enable the VBA development environment. In Excel, press Alt + F11. If that doesn’t work:
- Go to File > Options > Customize Ribbon
- In the main tabs panel, check Developer
- Click the Visual Basic icon
- A new window will open titled “Microsoft Visual Basic for Applications”
Double-click Sheet 1, then go to Insert > Procedure at the top menu. Give it a meaningful name like HighlightCarExpenses.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | Public Sub HighlightCarExpenses() Rem this is a comment Dim emptyCounter As Integer, currentRow As Integer Dim currentCell As Range Dim currentCellValue As String Rem Set initial value for the empty cell counter emptyCounter = 0 currentRow = 1 currentCellValue = "" Rem Scan all column B values Rem Stop scanning if we see 3 empty cells in column B in a row Do While (emptyCounter < 3) currentCellValue = ActiveSheet.Cells(currentRow, 2).Value Debug.Print "Value at row " & currentRow & ": " & currentCellValue If (currentCellValue = "") Then emptyCounter = emptyCounter + 1 ElseIf InStr(1, currentCellValue, "car", 1) Then Debug.Print "Car is found at row: " & currentRow ActiveSheet.Cells(currentRow, 2).Interior.Color = RGB(255, 0, 0) End If currentRow = currentRow + 1 Loop Debug.Print "last row " & currentRow End Sub |
The macro walks down column B and stops once it sees three empty cells in a row — a simple way to guess where the data ends without hard-coding a row count. InStr(1, currentCellValue, “car”, 1) does a case-insensitive substring search (the trailing 1 is the vbTextCompare flag), so “Car”, “CAR”, and “old car” all match.
A few things worth knowing.
RGB values go 0–255, not 0–256. The original macro had RGB(256, 0, 0) which is technically out of range — different Excel versions handle the overflow differently (some clamp to 255, some wrap, some raise an error). The version above uses RGB(255, 0, 0), which is the actual “pure red”. Easy mistake to make if you’re used to 0-indexed integers; VBA’s RGB isn’t.
Dim a, b As Integer doesn’t do what you’d expect. Coming from C, Java, or JavaScript, you’d assume Dim emptyCounter, currentRow As Integer declares both as Integer. It doesn’t — VBA only types the last variable on the line; emptyCounter ends up as Variant. The fix is to type each variable explicitly, as in the macro above: Dim emptyCounter As Integer, currentRow As Integer. This is genuinely one of VBA’s most copy-pasted bugs.
Conditional Formatting can do this without VBA. The original post said you couldn’t highlight “contains car” with a formula — that’s not quite true. Excel’s built-in Conditional Formatting has had a “Format only cells that contain → Specific Text → containing → car” rule for years, and it’ll do exactly what this macro does without writing any code. Reach for VBA when you need behaviour that Conditional Formatting can’t express — multi-step logic, data manipulation, talking to other workbooks — not for simple highlight-by-text.
VBA still works, but Office Scripts is the modern alternative. VBA is great on Windows desktop Excel and isn’t going anywhere, but if you’re on Excel for Mac or Excel on the web, look at Office Scripts — TypeScript-based, runs in Microsoft 365, and it’s the path Microsoft is investing in for cross-platform automation. Same kind of “loop down a column and tweak cells” model, just with modern syntax. 💡
Glossary
Comment: A part of the code (sentences) that will not be executed by the system. A way for the programmer to leave a note. In VBA, comments start with an apostrophe (‘) or the keyword Rem.
Debug: Trace value(s) to find and remove mistakes, or just to ensure program correctness. Debug.Print writes to the Immediate Window in the VBE (open it with Ctrl + G).
Dim: A declaration for a variable.
Sub: A procedure that runs but does not return a value. Public Sub means it can be called from other modules and shows up in the Macros dialog.
Range: A reference to one or more cells (e.g. a single cell, a row, a column, or a rectangular block). The base type for almost everything you do in Excel programmatically.
Integer: A whole-number data type. Examples: 1, 2, 3.
String: An alpha-numeric data type. Examples: “I have 2 dogs”, “Expenses are bad”. Strings are always written in double quotes.