Change Color of a Range of Cells Based on Cursor Location

[CorrieAnn]

Is there a way to change the color of a range of cells based on cursor location?

Scenario: I have a spreadsheet listing details for our employees such as title, dept, cell, company cell, DID numbers, etc. I do not want to use gridlines for various reasons. What would be helpful is if the attributes associated to the employee (columns O, X, Y, AE, AL, AR, AX and AZ) would change font color (white) when their name (located in column B) was clicked on.

Or maybe we can do this: if any cell in the row is active/highlighted...meaning, whichever row your cursor is on...that is the row where the font should be turned to white for those specific columns. I don't want the entire row because there is color-coded data captured in the row which will lose it's meaning if it gets turned to white.

 

[StephenCrump]

Yes, but you'll need VBA to do it. Is a VBA solution OK for you?

 

[bebo021999]

For "Selection event" : OK
For "Hover event" : as I know, it does not work for cell, but object.

 

[CorrieAnn]

Yes, but you'll need VBA to do it. Is a VBA solution OK for you?

Thank you, yes, that is what I am looking for. I am not strong in VBA as a programmer from scratch. I can read and edit, but that's about as good as it gets for me. Therefore, I will need assistance with a complete solution.

 

[StephenCrump]

You can use the Worksheet_SelectionChange event to capture the cursor row number every time the user changes the selection, and use this information to format/unformat the appropriate rows(s).

Here's one simple way you could do it, using conditional formatting. There are other variations that use VBA to turn the formatting off/on.

1. Using Name Manager, create a new Name, CursorRow, and give it a value, =1 (say).

2. In the VB Editor, create this Sub in the relevant sheet module - there are two dropdowns at the top of the screen you can use to select the Worksheet_SelectionChange event:

'In the Sheet module where you want the code to monitor the cursor, NOT a code module
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ThisWorkbook.Names("CursorRow").Value = Selection.Row
    
End Sub

3. Conditionally format the range as shown:

	A	B	C	D	E	F
1
2		Blah	Blah	Blah	Blah
3		Blah	Blah	Blah	Blah
4		Blah	Blah	Blah	Blah
5		Blah	Blah	Blah	Blah
6		Blah	Blah	Blah	Blah
7
8			To illustrate:
9			CursorRow	1
Sheet1

Cell Formulas
Range	Formula
D9	D9	=CursorRow

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:E6	Expression	=ROW()=CursorRow	text	NO

 

[kevin9999]

Alternative to Stephen's (excellent) suggestion. Assumes headers on row 1, data starts on row 2.
EDITED.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 1 Then
        lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
        For Each i In Array(15, 24, 25, 31, 38, 44, 50, 52)
            Cells(2, i).Resize(lr - 1).Font.Color = xlBlack
            j = ActiveCell.Row
            Cells(j, i).Font.Color = vbWhite
        Next i
    End If
End Sub

 

[CorrieAnn]

Alternative to Stephen's (excellent) suggestion. Assumes headers on row 1, data starts on row 2.
EDITED.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 1 Then
        lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
        For Each i In Array(15, 24, 25, 31, 38, 44, 50, 52)
            Cells(2, i).Resize(lr - 1).Font.Color = xlBlack
            j = ActiveCell.Row
            Cells(j, i).Font.Color = vbWhite
        Next i
    End If
End Sub

I am going to give this one a try. I have put the code in the worksheet module, but I need to adjust it for my headers being in row 6 and the data beginning in row 7. I changed the code to read as follows, but it didn't work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 1 Then
        lr = Cells.Find("*", , xlFormulas, , 6, 7).Row
        For Each i In Array(15, 24, 25, 31, 38, 44, 50, 52)
            Cells(7, i).Resize(lr - 1).Font.Color = xlBlack
            j = ActiveCell.Row
            Cells(j, i).Font.Color = vbWhite
        Next i
    End If
End Sub

 

[kevin9999]

I am going to give this one a try. I have put the code in the worksheet module, but I need to adjust it for my headers being in row 6 and the data beginning in row 7. I changed the code to read as follows, but it didn't work.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 1 Then
        lr = Cells.Find("*", , xlFormulas, , 6, 7).Row
        For Each i In Array(15, 24, 25, 31, 38, 44, 50, 52)
            Cells(7, i).Resize(lr - 1).Font.Color = xlBlack
            j = ActiveCell.Row
            Cells(j, i).Font.Color = vbWhite
        Next i
    End If
End Sub

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 6 Then
        lr = Cells.Find("*", , xlFormulas, , 1, 2).Row
        For Each i In Array(15, 24, 25, 31, 38, 44, 50, 52)
            Cells(7, i).Resize(lr - 1).Font.Color = xlBlack
            j = ActiveCell.Row
            Cells(j, i).Font.Color = vbWhite
        Next i
    End If
End Sub