In this Article
This tutorial will demonstrate how to use VBA to highlight a cell or a Range of cells.
Conditional formatting is one way of highlighting cells in Excel. We can also use VBA to perform cell highlighting.
Highlight a Cell
To highlight a cell in VBA, we can use the simple procedure as shown below.
Sub HighlightCell() ActiveCell.Interior.Color = vbRed End Sub
Highlight a Range of Cells
Similarly, we can select a Range of Cells, and highlight all the cells in the Range.
Sub HighlightRange() Range("A1:A10").Select Selection.Interior.Color = vbRed End Sub
Highlight Cells based on Cell Value
To add in a condition for highlighting a cell, we can use an IF statement to highlight the cell if the value of the cell is above a certain value, in this case greater than 10.
Sub HightlightCell_1 If ActiveCell.Value > 10 then ActiveCell.Interior.Color = vbRed End If End Sub
Highlight a Range of Cells based on Cell Value
To check the values in a Range of Cells, we would need to loop through each cell, establish it’s value and then highlight the cell accordingly. In the example below, we will first establish that the value held in the range is a number, and then test to see if that number is greater than 10.
Sub HighlightRangeOfCells() Dim rng As Range For Each rng In Range("A1:A10") If IsNumeric(rng.Value) Then If rng.Value > 10 Then rng.Interior.Color = vbRed End If End If Next rng End Sub
Highlight a Cell with Conditional Formatting
We can also use VBA to apply conditional formatting to a cell to highlight the cell. In the example below, conditional formatting will be applied to each cell in the range. As in the example above, we first test that the cell has a numeric value in it, and then apply the conditional formatting.
Sub SetConditionalFormatting() Dim rng As Range For Each rng In Range("A1:A10") If IsNumeric(rng.Value) Then rng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=10" rng.FormatConditions(1).Interior.Color = vbRed rng.FormatConditions(1).StopIfTrue = False End If Next rng End Sub
Highlight a Cell when Selection changes
We can make the highlighting of a cell dynamic when the cell pointer moves in the current worksheet by using the Worksheet_Change Event. In the example below, all the highlighting in the sheet will be removed (except those done by conditional formatting), and the activecell will be highlighted red (ColorIndex = 3).
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveSheet.Cells.Interior.ColorIndex = xlColorIndexNone Target.Interior.ColorIndex = 3 End Sub
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!