auto change color of active cell

In this Excel article, you will learn how to auto highlight the active cell in a sheet.

By default, Excel changes the border color of the active cell but here you will see how to change cell color when a new cell is selected.

Solutions for highlighting active cell in various scenarios are also given.

A simple way to highlight the active cell.
The highlight without deleting the original cell color of conditional format.
Highlight active cell in the protected sheet and in all the sheets or workbooks.

Quick and Simple way to highlight active cell in Excel

In the below GIF you can see active cell is getting auto-highlighted.

Note:- This method erases the original cell color as you can see in GIF. Use this method if you do not worry about preserving the original cell color.

If you want to keep the original cell color then go to 2nd subtopic of this article.

highlight-active-cell-method-1

To auto change color of active cell follow the below steps

Press ALT+F11 to open VBA IDE.
In VBA IDE Project Explorer (press CTRL+R if not visible) double click on the sheet (here Test sheet) for which you want to highlight the active cell.
Double click will bring the code area for that sheet.
Copy the below code section and paste it into that sheet module or code area.

highlight active cell VBA method 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static prevRng As Range
On Error Resume Next
  prevRng.Interior.ColorIndex = xlColorIndexNone
  ActiveCell.Interior.Color = vbGreen
  Set prevRng = Target
End Sub

Now you are ready to go. This will highlight the active cell of that sheet in which code is inserted.

Download File – highlight active cell simple method

Auto highlight active cell without erasing original cell color

In the below GIF you can see this method of highlighting active cell

Does not erase or delete the original cell color.
Keeps color of conditionally formatted cells ( in D column color is from conditional formatting)
You need not apply conditional formatting, VBA code auto applies conditional format on the active cell only.

highlight active cell without erasing original cell color

Below is the VBA code to change the active cell highlight color.

Don’t get afraid of such long lines of code, just follow the steps and everything will be fine.

Right mouse click on the sheet for which you want to highlight active cell then click on the View code option.
This will open the VBA code area for that sheet.
Copy-paste the below code section in that sheet module or sheet code area.

Alternatively

Press ALT + F11 to open the VBA IDE.
In Project Explorer (press CTRL+R if not visible) double click on the sheet (here Test sheet) for which you want to highlight the active cell to open the code area for that sheet.
Copy-paste the below code section in that sheet module or sheet code area.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
    highlightActiveCell
Application.ScreenUpdating = True
End Sub
Sub highlightActiveCell()
 Dim rng As Range, fc As FormatCondition
 Static prvRng As Range
 Dim formulaStr As String, condFrmt As FormatCondition
 Set rng = ActiveCell
 formulaStr = "=1=1"
 On Error Resume Next
 If Not prvRng Is Nothing Then
    For Each fc In prvRng.FormatConditions
       If fc.Type = xlExpression And fc.Formula1 = formulaStr Then fc.Delete
    Next fc
 Else
    For Each fc In ActiveSheet.Cells.FormatConditions
       If fc.Type = xlExpression And fc.Formula1 = formulaStr Then fc.Delete
    Next fc
 End If
 On Error GoTo 0
 Set condFrmt = rng.FormatConditions.Add _
 (Type:=xlExpression, Operator:=xlEqual, Formula1:=formulaStr)
 With condFrmt
    .Interior.Color = vbGreen
    .SetFirstPriority
 End With
 Set prvRng = ActiveCell
End Sub

In the above code subroutine or program named “highlightActiveCell” is called in selection change event (Worksheet_SelectionChange) of the worksheet.

“highlightActiveCell” sub runs automatically when selection is changed since it is called in selection change event of the worksheet.

Download file – highlight active cell kepping original cell color on a sheet

Highlighting cell when worksheet is protected

Highlight active cell VBA will throw an error if the worksheet is protected.

To avoid this error little modification in the program or subroutine named “Worksheet_SelectionChange” is needed.

You just need to unprotect the sheet before calling “highlightActiveCell” sub and re-protect it after calling it.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const pw As String = "abc"
Application.ScreenUpdating = False
ActiveSheet.Unprotect Password:=pw
    highlightActiveCell
ActiveSheet.Protect Password:=pw
Application.ScreenUpdating = True
End Sub

Changing cell highlight color in VBA code

In sub “highlightActiveCell” the below line gives color to active cell.

.Interior.Color = vbGreen

Method 1 change highlight color

Just replace vbGreen color constant with any one of the below color constants.

VBA-color-constants

Method 2 Using the color index method

You can also use color index between (1 to 56) to change cell color.

Just replace .Interior.Color = vbGreen with .Interior.ColorIndex = 4

At place of 4 you can use any number between 1 to 56.

There are many other methods to give color using VBA refer to the below video link for this

Color Related VBA – Part 2

The below video is a modified version of this topic where users can choose highlight color and disable and enable highlight feature using the shortcut key.

Highlight active cell automatically without erasing past cell colour in Excel (Method-2)

https://youtu.be/xssQo3HdrhA

Download file – Highlight active cell method -2 (modified version)

Make highlight feature available in all the Excel sheets

You may be wondering how to make this active cell highlight feature available in all the worksheets instead of a specific sheet.

The method is simple and you don’t need to paste code in each sheet module (sheet code area).

Press ALT + F11 to open VBA IDE.
Double click on the Thisworkbook module to open the coding area of thisworkbook.
Copy-paste the below code section in the Thisworkbook module.

highlight active cell in all worksheets

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = False
    highlightActiveCell
Application.ScreenUpdating = True
End Sub
Sub highlightActiveCell()
 Dim rng As Range, fc As FormatCondition
 Static prvRng As Range
 Dim formulaStr As String, condFrmt As FormatCondition
 Set rng = ActiveCell
 formulaStr = "=1=1"
 On Error Resume Next
 If Not prvRng Is Nothing Then
    For Each fc In prvRng.FormatConditions
       If fc.Type = xlExpression And fc.Formula1 = formulaStr Then fc.Delete
    Next fc
 Else
    For Each fc In ActiveSheet.Cells.FormatConditions
       If fc.Type = xlExpression And fc.Formula1 = formulaStr Then fc.Delete
    Next fc
 End If
 On Error GoTo 0
 Set condFrmt = rng.FormatConditions.Add _
 (Type:=xlExpression, Operator:=xlEqual, Formula1:=formulaStr)
 With condFrmt
    .Interior.Color = vbGreen
    .SetFirstPriority
 End With
 Set prvRng = ActiveCell
End Sub

Now after copy-pasting the above code section active cell highlight will work in all the sheets of the workbook.

If you notice carefully everything is same except

sub name “Worksheet_SelectionChange” is changed by “Workbook_SheetSelectionChange”.
All the code is written in the Thisworkbook module.

Here in Workbook_SheetSelectionChange, SheetSelectionChange means it is a selection change event and Workbook denotes, applicable on the workbook level means on all the sheets.

Download File – highlight active cell kepping original cell color in all the sheets

Highlight cells based on active cell value

In the below GIF you can see when a new cell is selected

Cells with value same as active cell value get auto highlighted in blue color
Cells with values less than the active cell value get auto highlighted in pink color.

So, you will see how to build this type of feature in Excel for a specific range.

highlight cells based on active cell value

Select range (here B5 to E11), and make sure 1st cell of selection is active.
Press ALT O D keys in series then click on New Rule. Alternatively, go to the Home tab then from the Conditional formatting drop down click on New rule.

highlight cells based on active cell value 1

Select “Use a formula to determine which cells to format” option. In formula write =B5=$B$1
This formula will highlight cells whose values are equal to the active cell.
Click on the Format button.

highlight cells based on active cell value 2

From Fill Tab select the color of your choice then click on the OK button.
In all the upcoming panes click on the Ok button.

highlight cells based on active cell value 3

Like this select the same range and apply 2nd conditional formatting to highlight cells whose values are less than the active cell value.
In 2nd conditional formatting use formula =B5<$B$1
Now if you write a number in cell B1. Cells with values equal to B1 will be highlighted in blue and cells with values less than B1 cell will be pink.
Now cell B1 value should be automatically updated to the active cell value instead of manual input.

highlight cells based on active cell value

To auto change cell B1 value , press ALT+F11 to open VBA IDE then double click on the sheet (here Test) in VBA IDE then copy-paste the below code section.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim marksRng As Range
 Set marksRng = Range("B5:E11")
 If Not Application.Intersect(Target, marksRng) Is Nothing Then
    Range("B1").Value = ActiveCell.Value
 End If
End Sub

The above code section will auto changes the B1 cell value when a new cell is selected.

Download file – Highlight cells based on active cell value

Take care of these things for active cell highlight in Excel

Since here VBA is involved hence you must save as your Excel file either in Binary (.xlsb) or in Macro (.xlsm) format.

If not saved either in Binary or Macro file format then VBA will be lost after closing the Excel file.

VBA event is involved hence make sure the VBA event is enabled, if not enabled highlight feature will not work.

To enable the event run the below code line in the immediate window.

Application.EnableEvents=True

Get most from VBA Immediate Window

Make sure macro is enabled and click on Enable macro and Enable Editing after downloading Excel files.

You may also like to read and learn the below topics

How to highlight alternate rows in Excel?

How to insert and run VBA code in Excel?

How to make Microsoft Excel Add in?