If you’re working with large spreadsheets, it can be challenging to keep track of which row you are currently working on. Fortunately, Excel has a feature that allows you to highlight the current row automatically. In this tutorial, we will show you how to use conditional formatting and VBA code to achieve this.
To start things off, head over to the Formulas tab and click on the Define Name button. Let’s name it “Current Row” and change the Refers To value to equal one. Click OK to save the changes.
Next, click on the corner button to select the entire worksheet. We need some conditional formatting, so go over to the Home tab and click on the Conditional Formatting drop-down button. Select the New Rule option and, in the pop-up, select the option for using a formula.
In the text box, type “equals Rho a one” followed by an equal sign and then the name we just created. Click the Format button to select how you want the row you are working on to be highlighted. Go to the Fill tab and select a color of your choice. Click OK in both pop-ups.
As you can see, we have our first row highlighted, but we want it to be controlled by the current row we’re working on. To do this, we need access to the Developer tab. If you don’t see it, go to File > Options > Customize Ribbon and check the Developer checkbox.
Once you have access to the Developer tab, click the Visual Basic button. Click on the sheet you are working on, select Worksheet from the first drop-down, and select the SelectionChange event from the second drop-down.
Under the SelectionChange event, type the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Rows(“1:” & Rows.Count).Interior.ColorIndex = 0
Target.EntireRow.Interior.Color = RGB(204, 255, 255)
End Sub
Close the window, and now, when you click on whatever row you are working on, it gets highlighted. And that’s all there is to it! We hope you found this tutorial useful.