Excel Tool

Order Online
Our company contact information There you can find frequently asked questions and answers

Home | Products |Purchase | FAQ | Contact Us | Useful Resources

Excel Highlight the Active (Current) Row / Colum (Cell)

Use conditional formatting to highlight the active (current) row, hard part is how to determine the active row:

Excel Highlight the Active (Current) Row or Colum (Cell)

VBA is easy, right-click the sheet tab and choose "view code" and copy and paste the following code in the VBE window:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ThisWorkbook.Names.Add "Mark", Target
End Sub

This is a few lines of code, when selecting a new cell, the cell ( Range ) is defined as the name "Mark" can refer to the names in the conditional formatting.

As the following Excel File, paste the code is complete, return the worksheet, select A4:I15, and click on the Home tab -> Conditional Formatting -> Manage Rules...:

Excel Highlight the Active (Current) Row or Colum (Cell)
Formula 1:= (A4 <> ") * (A4= Mark)
(If A4 is not empty, and A4 equals Mark)
Formula 2:=ROW () =ROW (Mark)
(If the line number of the current line number is equal to the Mark)

Example File: examination room.xlsm


1: Highlight Excel the Active Cell (Row or Column)

How to highlight a range of cells automatically start macros? Let one user's mouse click a worksheet cell, the corresponding rows and columns are displayed in one or two background colors:

Excel Highlight the Active (Current) Row or Colum (Cell)

Set the Excel background color using VBA directly, as follows:

Right mouse button click on the sheet tab, select the "view code" and paste the following code at the cursor:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Set Rng = Target.Range("a1")
Cells.Interior.ColorIndex = 0
'Clear all background color
Rng.EntireColumn.Interior.ColorIndex = 37
'Sets the active column color
Rng.EntireRow.Interior.ColorIndex = 43
'Sets the active row color
End Sub

Code comments:

37, 43 of which are colour index number, different values are represented in different colors.

Example File: highlight-excel-cell.xlsm


2: Set all the worksheets highlighted the active (current) row or column (cell) background color

If you want to make each sheet in the workbook has the same effect, what should I do?

Open the VBE Editor by Alt+F11, double-click the module "ThisWorkbook" in the VBAProject:

Excel Highlight the Active (Current) Row or Colum (Cell)

 paste the following code at the cursor:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim Rng As Range
Set Rng = Target.Range("a1")
Cells.Interior.ColorIndex = 0 'Clear all background color
Rng.EntireColumn.Interior.ColorIndex = 37 'Sets the current column color
Rng.EntireRow.Interior.ColorIndex = 43 'Sets the current row color
End Sub

Copyright © 2001-2014 Excel-Tool All Rights Reserved.

Copyright Excel-Tool All rights reserved