|
|
|
|
Hi all,
In B57:B96 I have names. In G57:G96 are the same names, but in a different order. When I activate (click) one of the names in G I want the background color of the B-cell with the same name to become yellow but only so long as the G-cell is activated.
Even better would be that the B-cell background is yellow as long as the cursor is over the G-cell with the same name (so without having to activate that G-cell).
Please help me with the code I need. Thanks in advance.
Hi all,
In B57:B96 I have names. In G57:G96 are the same names, but in a different order. When I activate (click) one of the names in G I want the background color of the B-cell with the same name to become yellow but only so long as the G-cell is activated.
Even better would be that the B-cell background is yellow as long as the cursor is over the G-cell with the same name (so without having to activate that G-cell).
Please help me with the code I need. Thanks in advance.
Here's an approach that uses a variant of a function that is not often useful but is perfect for this scenario. The code support is minimal and maybe you can even do without it.
The key is to use the CELL function as part of a conditional formatting formula.
My test: Highlight the data in C1:C20 that contained the same value as a selected cell in the range G1:G20.
So, select C1:C20 and create a conditional formatting formula
=IF(CELL("col")=COLUMN(G:G),C1=CELL("contents"),FALSE)
For the c.f. format I picked a background fill color.
Now, click on any cell in G1:G20 and press F9, which calculates the worksheet. The cells in C1:C20 with the same value as the selected cell in G will have the background fill visible.
To skip having to press the F9 key, add the code below in the worksheet's code module:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub
Sorry, can't do much with just hovering over the cell. But we can deal with one being selected. This is worksheet event code, so to put it into your workbook, open the workbook, select the sheet you need it to work on and right-click the sheet's name tab and choose [View Code] from the popup list.
Copy the code below and paste it into the code module presented to you. It must be the only
Private Sub Worksheet_SelectionChange(byVal Target As Range)
routine for that worksheet. If you already have one for it, we'll have to combine the existing code with this code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim foundMatch As Range
'reset all cells in column B, rows 57 through 96 to "no fill"
Range("B57:B96").Interior.ColorIndex = xlAutomatic
If Target.Cells.Count <> 1 Or Target.Column <> 7 Or _
Target.Row < 57 Or Target.Row > 96 Then
Exit Sub
End If
'one cell in G57:G96 has been selected
Set foundMatch = Range("B57:B96").Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not foundMatch Is Nothing Then
If Not IsEmpty(foundMatch) Then
foundMatch.Interior.ColorIndex = 6 ' yellow
End If
End If
End Sub
Hi all,
In B57:B96 I have names. In G57:G96 are the same names, but in a different order. When I activate (click) one of the names in G I want the background color of the B-cell with the same name to become yellow but only so long as the G-cell is activated.
Even better would be that the B-cell background is yellow as long as the cursor is over the G-cell with the same name (so without having to activate that G-cell).
Please help me with the code I need. Thanks in advance.
Here's an approach that uses a variant of a function that is not often useful but is perfect for this scenario. The code support is minimal and maybe you can even do without it.
The key is to use the CELL function as part of a conditional formatting formula.
My test: Highlight the data in C1:C20 that contained the same value as a selected cell in the range G1:G20.
So, select C1:C20 and create a conditional formatting formula
=IF(CELL("col")=COLUMN(G:G),C1=CELL("contents"),FALSE)
For the c.f. format I picked a background fill color.
Now, click on any cell in G1:G20 and press F9, which calculates the worksheet. The cells in C1:C20 with the same value as the selected cell in G will have the background fill visible.
To skip having to press the F9 key, add the code below in the worksheet's code module:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub
Thanks Jeeped, after I added
Dim bc As Range
Dim gc As Range
it worked like a charm.
Jeeped,
I expected there would be an event triggered by a "hoover" because hoovering works as a trigger in the case of a cell comment. Strange.
Indeed JL, foundmatch does the trick.
I expected there would be an event triggered by a "hoover" because hoovering works as a trigger in the case of a cell comment. Strange.
Sorry that I can't mark three replies as an answer, your reply certainly deserved to be marked as an answer ;–(
Hope you will nevertheless assist me in future occasions ;–)
Just like high school - first in line, last to get picked <grin>. Can I get a "Helpful"?
Of course I'll continue to assist you when I can.
I am curious whether or not you needed more than one cell to highlight at a time since my code only did one at a time and Jeeped offered one with multiple highlight capability.
Enter the thread ID of the thread you are merging into
To report abuse, sign in or continue without signing in
Thank you.
|
|
|
Don't have one of the above accounts?