Ever wonder how to highlight the active row in Excel with conditional formatting? Ever want to select a cell and see another cell highlighted if they have the same values?
This post applies to MS Excel 2007 and later, Mac Excel 2011 and later.
Excel Auto Highlight Row Based On Selected Cell Value
To highlight corresponding values in another column, you do not have to do it manually or use helper column.
Effect as shown below, and is done using conditional formatting.
Note that when the cell clicked does not have a corresponding value in column B, nothing will be highlighted.
Excel Highlight Row Based On Date
If you just want to highlight all cells in column A that is the same date as in cell B1, then you would select all cells from A2 to last cell in A.
Go to Home tab, Conditional Formatting, New Rule.
Select “Use a formula to determine which cells to format” and put as formula:
Excel Highlight Active Row Without VBA
While there is no Excel highlight active row shortcut, the method outlined here is pretty straight-forward.
The version without macro requires you to press F9 (this recalculates the workbook) each time you select a cell.
Select the cells in the “look up” column, where a corresponding value(s) shall exist. In this case, that is cells B2 to B11 (select from B2 downwards so that it is the active cell, so to speak)
In Home tab, Conditional Formatting, click New Rules, then “Use a formula to determine which cells to format”.
In the field for “Format values where this formula is true”, put
If instead of column B, you just want to highlight all cells in column A that correspond to a value in a fixed cell B1, then you would instead write
Here you would put dollar sign in front of B1 to fix the cell address, while leaving A2 unfixed so that the conditional formatting rule can pass on to other cells down the column.
Now whenever you select a cell in column A and press F9 after selection, it will highlight all the duplicate results in column B if there is one.
Auto Excel Highlight Active Row Using Excel VBA Macro
To avoid pressing F9 to recalculate each time, you will need to put a line of code in Visual Basic Editor. This will make the spreadsheet into a macro-enabled file with .xlsm extension.
Press Alt+F11 or Developer tab, Visual Basic icon, to open Visual Basic Editor.
To the left you should have a Project Explorer pane. If not, press Ctrl+R or go to View menu, Project Explorer.
Select the name of the workbook you are on by double-click, then the worksheet you are on.
Once selected, go to the main coding window to the right. The first step is to make sure that the Object field shows Worksheet.
Click the drop down in Procedure field, and choose “SelectionChange”. See screenshot:
Remove what is already there, then put these three lines of code there:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Close the Visual Basic Editor then try again on your workbook. Remember to save the workbook if you want to use it later.
How To Auto Highlight A Row In Excel While Scrolling
If your aim is to highlight a row of your selected cell after scrolling, the above technique will work.
If your aim is for Excel to always auto highlight the top row while you scroll down, you will need to write a macro.
Firstly go to name manager and create a new name called “top_row”, set its value as 1.
Suppose your data runs from cell A1 to C27. Select from A1 onwards.
Open Conditional Formatting and put the formula:
In VBA Editor, double click on the sheet of interest in Project Explorer.
Clear what is in the coding pane, and put this instead:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Names("top_row").RefersToR1C1 = ActiveWindow.VisibleRange.Row End Sub
You can modify the code to either highlight a row that is a fixed distance from the top row, or to highlight a row without having to click on a random cell everytime you scroll (which is a longer piece of script that does not involve conditional formatting).
Auto Highlight Entire Row / Excel Highlight Active Row
To highlight the active cell’s row in Excel as well as the column, you would select all the cells where selection might take place. In this case the order of selection is not important since the formula that follows does not use a direct cell address.
Go to Home tab, Conditional Formatting, New Rule.
Select “Use a formula to select which cells to format”.
Put in the following formula:
Press the Format button to select a highlight colour.
Do the same to column by pressing New Rule button in Conditional Formatting Rules Manager window:
Again choose “Format values where this formula is true”.
Put the following formula:
Press Format button, then select the same formatting as you did for rows.
As in the section above, you will need to press F9 each time after you select a cell. If you wish to automate it, see the Macro section.
You can use the second technique on “Excel highlight active row” above in the first one so that when you select a cell, both the cell and the corresponding cells get highlighted.
Excel Highlight Active Row Note and Warning
If you apply conditional formatting to a large amount of cells, it may slow down Excel.
Also the macro code inserted has the same effect as pressing F9 to refresh the workbook.
If the techniques shown here brought you pleasant surprises, share the love on social media. It’s free.
A Product That Highlights Every Other Row
If you simply want to highlight alternate rows in different colours (so-called zebra stripes), check out Text Manipulation Macro that comes with checkbox alignment tool.