Code:
Copy Code code as follows:
Private Sub Worksheet_selectionchange (ByVal Target as Range)
ThisWorkbook.Names.Add "XM", Target
End Sub
The effect of these lines of code is to define the cell (or range) as name "XM" When you select a new cell, and you can reference that name in conditional formatting settings.
As 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 equals the line number of XM)
Define the name in code, we need its immediate updates, what the mouse points to, this name "XM" updates what.
For example, when the mouse clicks on the F7 cell, XM equals F7,row (XM) = 7, and in the area where the conditional format is set, the cell with the row () equal to 7 (which is plainly the 7th row) displays the background color.
The same effect can be achieved by manual definition, try it. Then compare the benefits of using code.
When B6 cells are selected, there is a difference, because there are two conditional formats in the zone, and the first one is displayed with the first condition, and the second is displayed if the second condition is met.
Click on the menu "format-conditional format" to see clearly.
Is there a problem with beginners? Can you make the highlighted cell automatically start a macro, let the user's mouse click on a worksheet cell, the corresponding row and column display one or two background colors!
Set the background color directly with VBA by doing the following:
Right-click the sheet tab, select "View Code", and paste the following code at the cursor:
Code:
Copy Code code as follows:
Private Sub Worksheet_selectionchange (ByVal Target as Range)
Dim Rng as Range
Set Rng = Target.range ("A1")
Cells.Interior.ColorIndex = 0 ' Clears all background colors
Rng.EntireColumn.Interior.ColorIndex = 40 ' Sets the current column color
Rng.EntireRow.Interior.ColorIndex = 36 ' Sets the current row color
End Sub
Where 40, 36 is the color index number, different values represent different colors, the corresponding index is as follows:
What should you do if each worksheet in your 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 at the cursor:
Code:
Copy Code code as follows:
Private Sub Workbook_sheetselectionchange (ByVal Sh as Object, ByVal Target as Range)
Dim Rng as Range
Set Rng = Target.range ("A1")
Cells.Interior.ColorIndex = 0 ' Clears all background colors
Rng.EntireColumn.Interior.ColorIndex = 40 ' Sets the current column color
Rng.EntireRow.Interior.ColorIndex = 36 ' Sets the current row color
End Sub
After using this code, the "copy" and "copy" functions in the table are prohibited, do not know if there is any way to solve?
You can insert a line of code in the second line of your code (before you clear the color):
If Application.cutcopymode Then exit Sub ' exits the program if it is in the selected state
Code:
Copy Code code as follows:
Private Sub Workbook_sheetselectionchange (ByVal Sh as Object, ByVal Target as Range)
If Application.cutcopymode Then exit Sub ' exit program if in selection
Dim Rng as Range
Set Rng = Target.range ( "A1")
Cells.Interior.ColorIndex = 0 ' Clear all background colors
Rng.EntireColumn.Interior.ColorIndex = 40 ' Sets current column color
Rng.enti ReRow.Interior.ColorIndex = 36 ' Sets the current row color
End Sub
