How To Change Color Of The Row With A Click Using VBA In Microsoft Excel 2010

In this article, you will learn how to change color of the row with a click.

Click on Developer tab

From Code group select Visual Basic
 

 
Enter the following code in the current worksheet (sheet1 in our example)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rownumber As Integer

rownumber = ActiveCell.Row

    If ActiveCell.Value <> "" Then

        Range("A" & rownumber & ":D" & rownumber).Interior.ColorIndex = 6

    End If

End Sub
 

 
The SelectionChange event will get activated every time the user selects any cell & it will change the color from white to yellow color. If active cell is empty then, the code will not run.

If we click on cell B3 then, the row will be highlighted in yellow color. Refer below shown snapshot
 

 
Here, you can see that the formula bar shows selected cell i.e. cell B3

If we select cell A8 then, row 8 will get highlighted. Refer below shown snapshot.
 

 
In case you want to highlight only single row at a time then you need to add one line to the previous code.

Added code:

Range("A1:D13").Interior.ColorIndex = xlNone

The code becomes:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rownumber As Integer

rownumber = ActiveCell.Row

    If ActiveCell.Value <> "" Then

    Range("A1:D13").Interior.ColorIndex = xlNone

    Range("A" & rownumber & ":D" & rownumber).Interior.ColorIndex = 6

    End If

End Sub

Now, only one row will get highlighted at one point of time.

In this way, you can highlight the rows with a single click, using VBA code.