Recently I received a question from our reader through our facebook page about how to highlight selected cell in excel with color. I have crated this short VBA codes for following to answer his question:

1. VBA code to highlight selected cell in a single sheet.
2. VBA code to highlight selected cell in all sheets
3. VBA code to highlight active row and column with different color

With these VBA codes, the selected area or row and column will be highlighted with the predetermined color. This will change, If we change the selection.

Following Points to be noted while using this code in excel file.

• Macros should be enabled in excel.
• File should be saved in .xlsm format since the macro is created in VBA.
• This will not impact the existing color in selected area.
• Different code should be used in case if we want to use single sheet and for entire file as below.

VBA code to highlight selected cell in a single sheet.

In case if you want to highlight the selected cells with color in a single sheet, we can use the below code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Selection.Worksheet.Cells.FormatConditions.Delete
Selection.FormatConditions.Add xlExpression, , "TRUE"
Selection.FormatConditions(1).Interior.Color = vbYellow
End Sub

• Right click on sheet name and click on View Code or press Alt+F11 to open VBA code editor
• Salect the sheet in the VBA editor
• copy and paste the above code as below

In this code the Yellow color is selected. We can change the color if required.

VBA code to highlight selected cell in all sheets.

You can apply this to all sheets in a particular excel workbook with the below code.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Selection.Worksheet.Cells.FormatConditions.Delete
Selection.FormatConditions.Add xlExpression, , "TRUE"
Selection.FormatConditions(1).Interior.Color = vbYellow
End Sub

• Right click on sheet name and click on View Code or press Alt+F11 to open VBA code editor
• Salect this workbook in the VBA code editor
• copy and paste the above code as below

Highlight active row and column with different color.

When you work on a large worksheet, it is useful if the active row and column is highlighted with color. To highlight both row and column with different color, you can use the below code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Selection.Worksheet.Cells.FormatConditions.Delete
Rows(ActiveCell.Row).FormatConditions.Add xlExpression, , "TRUE"
Rows(ActiveCell.Row).FormatConditions(1).Interior.Color = vbYellow
Columns(ActiveCell.Column).FormatConditions.Add xlExpression, , "TRUE"
Columns(ActiveCell.Column).FormatConditions(2).Interior.Color = vbGreen

End Sub

You can follow the above mentioned steps to copy paste this code to VBA code editor.

Try this code and give your feedback below. Please share if you know any other method to do this.