Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
"XM", Target
End Sub
The purpose of these lines of code is that when selecting a new cell, the cell (or region) is defined as the name "XM", which can be referenced in the conditional formatting settings.
As in the following attachment, after pasting the code, return to the worksheet, select A4:I15, click the menu "Format - Conditional Format" and set:
Formula 1: =(A4<>"")*(A4=XM)
(If A4 is not empty and A4 is equal to XM)
Formula 2: =ROW()=ROW(XM)
(If the current line number is equal to the line number of XM)
Define the name with code, we need its instant update. Whatever the mouse clicks, the name "XM" will be updated.
For example, when the mouse clicks on cell F7, XM is equal to F7, ROW(XM)=7, in the area where the conditional format is set, any cell with Row() equal to 7 (to put it bluntly, line 7) will display the background color.
The same effect can be obtained by manually defining it, you can try it. Then compare what are the benefits of using code.
When cell B6 is selected, there is a different situation because two conditional formats are set in the area. If it meets the first condition, it displays the first format, and if it meets the second condition, it displays the second format.
Click the menu "Format—Conditional Format" to see clearly.
Have a question for beginners? Can the highlighted cell be made into an automatically started macro so that after the user's mouse clicks a cell in the worksheet, the corresponding rows and columns will display one or two background colors!
Directly use VBA to set the background color, the method is as follows:
Right-click the worksheet label, select "View Code", and paste the following code to the cursor:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Set Rng = ("a1")
= 0 'Clear all background colors
= 40 'Set the current column color
= 36 'Set the current line color
End Sub
Among them, 40 and 36 are color index numbers, and different values represent different colors, and the corresponding index is as follows:
What should I do if each worksheet in the workbook has the same effect?
Press Alt+F11 to open the VBE editor, double-click the module "ThisWorkbook" in the project manager, and paste the following code to the cursor:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim Rng As Range
Set Rng = ("a1")
= 0 'Clear all background colors
= 40 'Set the current column color
= 36 'Set the current line color
End Sub
After using this code, the "copy" and "copy" functions in the table are banned. I wonder if there is a way to solve it?
You can insert a line of code in the second line of the code (just before clearing the color):
If Then Exit Sub 'If it is selected, exit the program
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Then Exit Sub 'If it is selected, exit the program
Dim Rng As Range
Set Rng = ("a1")
= 0 'Clear all background colors
= 40 'Set the current column color
= 36 'Set the current line color
End Sub
Copy the codeThe code is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
"XM", Target
End Sub
The purpose of these lines of code is that when selecting a new cell, the cell (or region) is defined as the name "XM", which can be referenced in the conditional formatting settings.
As in the following attachment, after pasting the code, return to the worksheet, select A4:I15, click the menu "Format - Conditional Format" and set:
Formula 1: =(A4<>"")*(A4=XM)
(If A4 is not empty and A4 is equal to XM)
Formula 2: =ROW()=ROW(XM)
(If the current line number is equal to the line number of XM)
Define the name with code, we need its instant update. Whatever the mouse clicks, the name "XM" will be updated.
For example, when the mouse clicks on cell F7, XM is equal to F7, ROW(XM)=7, in the area where the conditional format is set, any cell with Row() equal to 7 (to put it bluntly, line 7) will display the background color.
The same effect can be obtained by manually defining it, you can try it. Then compare what are the benefits of using code.
When cell B6 is selected, there is a different situation because two conditional formats are set in the area. If it meets the first condition, it displays the first format, and if it meets the second condition, it displays the second format.
Click the menu "Format—Conditional Format" to see clearly.
Have a question for beginners? Can the highlighted cell be made into an automatically started macro so that after the user's mouse clicks a cell in the worksheet, the corresponding rows and columns will display one or two background colors!
Directly use VBA to set the background color, the method is as follows:
Right-click the worksheet label, select "View Code", and paste the following code to the cursor:
Code:
Copy the codeThe code is as follows:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rng As Range
Set Rng = ("a1")
= 0 'Clear all background colors
= 40 'Set the current column color
= 36 'Set the current line color
End Sub
Among them, 40 and 36 are color index numbers, and different values represent different colors, and the corresponding index is as follows:
What should I do if each worksheet in the workbook has the same effect?
Press Alt+F11 to open the VBE editor, double-click the module "ThisWorkbook" in the project manager, and paste the following code to the cursor:
Code:
Copy the codeThe code is as follows:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim Rng As Range
Set Rng = ("a1")
= 0 'Clear all background colors
= 40 'Set the current column color
= 36 'Set the current line color
End Sub
After using this code, the "copy" and "copy" functions in the table are banned. I wonder if there is a way to solve it?
You can insert a line of code in the second line of the code (just before clearing the color):
If Then Exit Sub 'If it is selected, exit the program
Code:
Copy the codeThe code is as follows:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Then Exit Sub 'If it is selected, exit the program
Dim Rng As Range
Set Rng = ("a1")
= 0 'Clear all background colors
= 40 'Set the current column color
= 36 'Set the current line color
End Sub