Automatically color rows and columns when clicking on cells in Excel

In the process of working with Excel files, setting up a spreadsheet according to comparative values, horizontal or vertical comparison is a part and also a fairly basic requirement of the application.

And to help you highlight the values ​​in rows, or columns that need to be compared on Excel files more conveniently, in today’s article I will share with you a little trick in coloring values. mouse pointer on the worksheet in rows and columns, or both vertical and horizontal axes with simple VBA code. Assist you in controlling and checking data on spreadsheets more easily…

Read more:

Okay, with that said, you probably already understand what I want to cover in this tutorial, right? Then let’s get started!

#first. How to color Excel in rows when clicking on cells in Excel

+ Step 1: First, you open the Excel file to be processed.

Here, you open the Developer tab on the Ribbon => and select Visual Basic or press the key combination Alt + F11 to open the VBA code editor.

+ Step 2: At the window Microsoft Visual Basic for Applications, you click on Sheet currently in Project VBAProject to open the dialog box Sheet (Code).

=> Then you Copy the following code and Paste it in before pressing Alt + Q to close this window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Cells.Interior.ColorIndex = 0
ActiveCell.EntireRow.Interior.ColorIndex = 8
Application.CutCopyMode = True
End Sub

+ Step 3: Now you can click the mouse pointer at the Excel file interface to see the results

#2. How to color Excel by column when clicking on a cell in Excel

Similar to how to color in rows in Excel file. To color and highlight column values ​​when the mouse pointer is over, you need:

+ Step 1: Press the key combination Alt + F11 to open the VBA code editor.

+ Step 2: Then double click Sheet hiện tại at the window Project VBAProject => and Paste the code below into the dialog box Sheet (Code).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Cells.Interior.ColorIndex = 0
ActiveCell.EntireRow.Interior.ColorIndex = 8
ActiveCell.EntireColumn.Interior.ColorIndex = 8
Application.CutCopyMode = True
End Sub

+ Step 3: Then click on the Excel file to test it

#3. How to color rows and columns in Excel (when clicking on a cell in Excel)

In addition to the two ways to conditionally color the row and column values ​​above. If you need to compare data in both rows and columns, you can use the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Cells.Interior.ColorIndex = 0
ActiveCell.EntireRow.Interior.ColorIndex = 8
ActiveCell.EntireColumn.Interior.ColorIndex = 8
Application.CutCopyMode = True
End Sub

Or you use the following code:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
‘Update 20140318’
Static xRow
Static xColumn
If xColumn <> “” Then
With Columns(xColumn).Interior
.ColorIndex = xlNone
End With
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
EndIf
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
With Rows(pRow).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End Sub

Paste into the dialog box Sheet (Code). At the dialog box Microsoft Visual Basic for Applications.

Then click on any cell in the Excel file to see the results.

Epilogue

Okay, that’s it. I have just guided you in detail with small tricks in automating Colorize rows and columns when clicking on cells in Excel okay then.

I think this trick will be very helpful for you in comparing, contrasting, or reviewing information by rows or columns more conveniently and easily before long and complex spreadsheet lists.

At this point, the article on how to automatically color in rows and columns when clicking on a cell on my Excel file will also be paused. Hope this trick will be useful to you.

Good luck !

CTV: Luong Trung – techtipsnreview