How to automatically color rows and columns in Excel

In order to clarify the content in Excel data sheet, or to highlight the data, we have the option of alternating the Excel lines or coloring the alternate cells for Excel, depending on the content that needs attention at where. Then the color displays alternately for all data objects in the table.

In case the user does not want to interleave colors but only wants to get the color when clicking first, then you can use VBA code. In this way, the data table will highlight the values ​​in rows or columns when collated, by clicking the mouse when needed. The following article will show you how to automatically match color in Excel.

1. How to change spreadsheet color lines in Excel
2. How to format borders and background colors for Excel tables
3. Change color between different lines in Microsoft Excel
4. Instructions on how to create diagonal lines in Excel box

1. How to color Excel rows when you click

Step 1:

In the Excel file you need to process, click the Developer tab on the Ribbon and choose Visual Basic or press Alt + F11 to open the VBA code editor.

How to automatically color rows and columns in Excel Picture 1

Step 2:

Displaying the Microsoft Visual Basic for Applications interface, we click on the current sheet in Project VBAProject to open the Sheet dialog.

How to automatically color rows and columns in Excel Picture 2

Next, enter the following code into the interface and press Alt + Q to turn off this interface.

 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 

How to automatically color rows and columns in Excel Picture 3

Step 3:

Back to the Excel data sheet interface when we click the mouse pointer on a cell, the row will be colored as shown below. Automatic rows are colored when you click.

How to automatically color rows and columns in Excel Picture 4

2. How to color Excel columns automatically

Step 1:

We also press Alt + F11 to open the VBA code editor. At this interface you continue to double-click on the currently open sheet in the Project VBAProject interface. Enter the code below into the VBA interface and also press Alt + Q to turn off this interface.

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

How to automatically color rows and columns in Excel Picture 5

Step 2:

As a result, when you click on any one cell, the column will be colored as shown below.

How to automatically color rows and columns in Excel Picture 6

3. How to color rows and columns in Excel

When you request to color columns or rows by value, use the above two ways. In case of comparison due to data comparison, we are forced to color both in rows and columns.

Step 1:

In the VBA code entry interface, you also click on the working sheet then enter 1 of the 2 code snippets below.

 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 use the code below if the code is problematic.

 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 End If 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 

How to automatically color rows and columns in Excel Picture 7

Step 2:

After closing the interface to enter this VBA code, the user clicks any number and sees that both the column and the row passing through that cell are colored differently from the remaining cells.

How to automatically color rows and columns in Excel Picture 8

The color for the boxes when marked will depend on the ColorIndex color value table. To change the color of the column when highlighted, we need to change the number of values ​​shown for each color according to this table.

How to automatically color rows and columns in Excel Picture 9

To change the color we need to dim the interface to enter the code in VBA, then change the number of values ​​in ColorIndex = 8 to ColorIndex = 40 for example.

How to automatically color rows and columns in Excel Picture 10

Then the line color in Excel is converted to another color.

How to automatically color rows and columns in Excel Picture 11

With the type of color for the column and the user box can completely combine 2 different colors in the code.

How to automatically color rows and columns in Excel Picture 12

Excel column and row results have changed into 2 different colors as shown below.

How to automatically color rows and columns in Excel Picture 13

Another way to color rows and columns in Excel. Just enter the correct code, the row and column when clicked are all colored, easily collated, compared or searched for information in rows or columns, especially with long spreadsheets.

I wish you all success!