To highlight worksheet cells while looping through an array in Excel VBA, read a range into an array, test each array value, and apply a fill color to the matching cell in the original range. The array holds values, not formatting, so the macro must use the array index to identify the corresponding worksheet cell.
What an array loop can—and cannot—highlight
A VBA array is a structure in memory. It can hold values read from cells, but it does not carry the worksheet cells’ fill colors, borders, or other formatting. Changing an array element therefore does not change the appearance of the cell from which the value came.
To color the matching cell, use the loop’s index with a worksheet range. For example, if the array was loaded from A2:A100, array item 1 corresponds to A2, item 2 corresponds to A3, and so on. The expression rng.Cells(i, 1) points to the worksheet cell at the same relative position as item i in the array.
This distinction is useful when a macro needs to examine many values efficiently. It can read the range once, process the values in memory, and then apply formatting to cells that meet a condition. The values are checked in the array; the formatting is applied to the worksheet.
Example: highlight cells containing a status
This example checks A2:A100 on a worksheet named Sheet1 and colors cells containing “Late” yellow. It uses Value2 to load the range into a two-dimensional array. Because the specified range has multiple cells, VBA returns an array with row and column indexes, even though the range has only one column.
Sub HighlightLateCells()
Dim ws As Worksheet
Dim rng As Range
Dim data As Variant
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("A2:A100")
' Read the cell values into memory once.
data = rng.Value2
' Check each array value and color its source cell.
For i = LBound(data, 1) To UBound(data, 1)
If Not IsError(data(i, 1)) Then
If StrComp(CStr(data(i, 1)), "Late", vbTextCompare) = 0 Then
rng.Cells(i, 1).Interior.Color = vbYellow
End If
End If
Next i
End Sub
Change the worksheet name and range to fit the workbook. The comparison uses vbTextCompare, so “Late,” “late,” and “LATE” all match. The error check prevents a cell containing an Excel error value from causing a type mismatch. Blank cells and other nonmatching values are left alone.
The macro does not remove existing fills from cells that fail the test. That is deliberate: clearing every fill in the range could erase formatting applied for another reason. If the range is reserved for this macro’s results, you can add a reset step before the loop, but only after confirming that removing its existing fills is acceptable.
How to run the macro safely
- Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z.
- In Excel for Microsoft 365 on Windows, open the workbook in the desktop app and press Alt+F11 to open the Visual Basic Editor. On Mac, use Option+F11; some keyboards also require Fn. Excel for the web cannot run VBA macros.
- In the editor, choose Insert > Module, then paste the code into the module. Change
Sheet1andA2:A100if necessary. - Return to Excel and run the procedure from the Visual Basic Editor with F5, or use Alt+F8 in Excel on Windows to select the macro. On Mac, the function-key behavior can depend on keyboard settings; the Developer tab’s Macros command is another way to run it.
For a workbook that must retain VBA code, save it as an Excel Macro-Enabled Workbook (.xlsm). Excel may display a security warning when opening a file with macros; enable macros only when you trust the file and its source.
Adjusting the condition and color
The loop’s If test determines which values qualify. The color assignment determines what happens to a qualifying cell. You can change either part without changing the basic relationship between the array index and the worksheet cell.
- Match a different word: replace
"Late"with another status, such as"Review". - Use a different fill: replace
vbYellowwith a VBA color constant such asvbRed, or use an RGB color expression such asRGB(255, 200, 0). - Color the font instead: use
rng.Cells(i, 1).Font.Color = vbRedinstead of settingInterior.Color. - Check a numeric threshold: test whether a value is numeric before comparing it. For example, use
If Not IsError(data(i, 1)) And IsNumeric(data(i, 1)) Then, then place a separate numeric comparison inside that block.
VBA’s And operator does not short-circuit evaluation: both sides of an And expression are evaluated. For that reason, do not combine an error check and a conversion that could fail into one condition. Keep the error check in its own If block, as in the main example.
Using a two-dimensional range
When a range has several columns, the array has a row and a column index. Loop through both dimensions, then use those same indexes with the source range. This example colors cells in B2:D20 whose values equal “Check”; it does not color other cells in the same row.
Sub HighlightChecksInBlock()
Dim ws As Worksheet
Dim rng As Range
Dim data As Variant
Dim r As Long
Dim c As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set rng = ws.Range("B2:D20")
data = rng.Value2
For r = LBound(data, 1) To UBound(data, 1)
For c = LBound(data, 2) To UBound(data, 2)
If Not IsError(data(r, c)) Then
If StrComp(CStr(data(r, c)), "Check", vbTextCompare) = 0 Then
rng.Cells(r, c).Interior.Color = RGB(255, 230, 153)
End If
End If
Next c
Next r
End Sub
Using rng.Cells(r, c) is important: the indexes are relative to the top-left corner of rng, not absolute worksheet row and column numbers. Thus array position (1, 1) corresponds to B2 in this example. If you instead use a worksheet reference such as ws.Cells(r, c), the code would point near the top-left of the sheet, not to B2:D20.
Choosing between an array loop and a direct cell loop
A direct loop reads each cell from the worksheet as it goes and can be easier to understand for a small range. An array loop reads the values into memory first, which can reduce repeated communication between VBA and the worksheet when processing a large range. The array approach is especially useful when the macro also needs to calculate, compare, or transform many values.
However, loading values into an array does not make every operation instantaneous. If the macro formats thousands of individual cells, each formatting assignment still acts on the worksheet. For straightforward rules such as “highlight values greater than 100,” Excel’s built-in conditional formatting may be simpler and can update automatically when values change. A VBA macro is more appropriate when the rule involves custom processing, a one-time action, or steps that conditional formatting cannot express conveniently.
For a direct loop, the equivalent cell reference is often written as cell inside a For Each loop. That avoids keeping an array index aligned with a range, but it reads cell values individually. Choose the approach based on range size, rule complexity, and whether the result should update automatically—not just on which version uses fewer lines.
Common errors and how to fix them
- “Subscript out of range” on the worksheet line: the sheet name may not match the tab name, or the sheet may not be in the workbook referenced by
ThisWorkbook. Check spelling and confirm where the code is stored. - Wrong cells are highlighted: make sure the array and the range used for formatting start from the same top-left cell and have the same dimensions. Use
rng.Cells(i, 1)for a one-column range, orrng.Cells(r, c)for a block. - Only some text matches: the main example ignores letter case, but it still requires the cell value to equal “Late.” A value such as “Late – supplier” is not an exact match. Use a suitable text-search condition if partial matches are intended.
- Type mismatch: a cell may contain an Excel error such as
#N/A. Test withIsErrorbefore converting or comparing its value. - No color appears: confirm that macros are enabled, that the procedure ran, and that the test value is inside the specified range. Also check whether conditional formatting or another macro is changing the displayed appearance.
One important edge case is a range containing exactly one cell. Assigning that cell’s Value2 to a Variant produces a single value rather than the two-dimensional array returned for a multi-cell range. The example deliberately uses a multi-cell range, A2:A100. If you change it to a one-cell range, add separate handling for that case or use a direct cell loop.
When conditional formatting is a better fit
If the goal is simply to keep cells highlighted whenever their values meet a rule, conditional formatting avoids running a macro again after each edit. On Windows, select the cells and use Home > Conditional Formatting to create a rule. For a basic text or number test, choose a suitable built-in rule; for a custom condition, use a formula-based rule and set the desired format.
Conditional formatting is not a VBA array loop: it stores a rule in the worksheet and recalculates the displayed format as values change. A macro applies formatting at the time it runs, so the color can become out of date if the underlying values later change. Use conditional formatting for live visual rules and VBA for actions that need programmatic control or more involved processing. Excel for the web supports many worksheet formatting features, but it does not run VBA; use desktop Excel to run the macro.
Frequently Asked Questions
Can I highlight an array element without referring to a worksheet cell?
No. An array in VBA has no worksheet formatting. To show a color in Excel, apply the formatting to a Range or another worksheet object corresponding to that array element.
Why does the array start at 1 instead of the worksheet row number?
Array indexes are relative to the data loaded from the range. If the range starts at A2, its first array row is normally 1, even though the corresponding worksheet row is 2. Use the same relative index with rng.Cells to map it back correctly.
How do I highlight a whole row when one array value matches?
Keep the array index for the matching row, then format the corresponding worksheet row or a specific row range. For example, if the source range begins at B2, array row 1 maps to worksheet row 2. Apply formatting to the desired columns on that worksheet row rather than to the single source cell.
Will the highlight update when the cell value changes?
Not automatically. The macro applies a fill when it runs; run it again to refresh the results. If the highlight should change dynamically with the value, use conditional formatting instead.
Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to run the procedure.
Related
- Excel Text to Columns: Why Leading Zeros Disappear
- Excel Text to Columns: What the Text Qualifier Does
- Excel Text to Columns: Split at the First Delimiter
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Copy and Paste: Practical Macro ExamplesTo copy a range in Excel VBA, use Range.Copy Destination:= followed by the top-left destination cell; Excel copies…
- Excel VBA Set Cell Color: ColorIndex, RGB, and MoreTo set a cell’s fill color with VBA, assign a color to its Interior.Color property—for example, use RGB(255,…
- VBA Code to Delete Rows in Excel: Practical ExamplesIn Excel VBA, delete a worksheet row with Rows(rowNumber).Delete; when removing rows that meet a condition, loop from…
- Excel VBA On Error Resume Next: Safe Use and ExamplesIn Excel VBA, On Error Resume Next tells a procedure to continue with the statement immediately after the…
- Access VBA String to Number: Safe Conversion GuideIn Access VBA, convert a numeric string with the function that matches the number you need: use CLng…
- Combobox in VBA UserForms: Setup, Code, and TipsA ComboBox in a VBA UserForm lets a person choose an item from a drop-down list, and it…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.