VBA Highlight Cell

This tutorial will demonstrate how to use VBA to highlight a cell or a Range of cells.

With VBA we can use Excel’s Conditional Formatting to highlight cells or we can use other methods to similar effect.

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 Easy

Stop 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!


Learn More!