How to highlight cross rows in Excel

In an Excel table, when there is a lot of data, it is easy to misread the row and column data. Today I will teach you a cross highlighting technique. When the data point reaches a certain point, the row and column will be automatically highlighted. Don’t worry about reading the wrong data!

How to highlight cross rows and columns in Excel

1. I usually use WPS for making tables

WPS has an advantage in this function. The setting is relatively simple. You only need to activate the reading mode under the view menu bar, and then select a cross color arbitrarily.

Recommended learning: excel basic tutorial

2. OFFICE Excel

The setting in the Excel table is relatively complicated. There are two situations and two methods:

● Suitable for when conditional formatting is not set in the worksheet

We are Under Visual basic of the development tool, or directly press ALT F11

to enter a string of codes in thisbook:

The code is as follows, you can copy and paste it directly. If you need a different color, you only need to change 20 to other numbers

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) 
    Cells.FormatConditions.Delete 
        With Target.EntireColumn 
            .FormatConditions.Add xlExpression, ， "=true" 
            .FormatConditions(1).Interior.ColorIndex = 20 
        End With 
        With Target.EntireRow 
            .FormatConditions.Add xlExpression, ， "=true" 
            .FormatConditions(2).Interior.ColorIndex = 20 
        End 
WithEnd Sub

The effect is as follows:

This code is applicable if there is no conditional formatting setting. If it is set, then use the following method

● It is applicable to the worksheet without any cell mark color

Similarly Press ALT F11 in the table, then enter the code:

where the code is:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Sh.Cells.Interior.ColorIndex = xlNone
     Target.EntireColumn.Interior.ColorIndex = 36 
     Target.EntireRow.Interior.ColorIndex = 36
 End Sub

This time we use 36, when we click on the cell , the display effect is yellow, as shown below:

Of course, when there is useful code in the Excel table, you need to save the table as xlsm format

Next time you need to cross-display data, use the above techniques to set it up. Have you learned it? Give it a try~

For more Excel-related tutorials, please pay attention to PHP Chinese website!

The above is the detailed content of How to highlight cross rows in Excel. For more information, please follow other related articles on the PHP Chinese website!