VBA Code To Highlight Row And Column Where Cursor Is

[data808]

I am currently using this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False

'Clear the color of all cells
Cells.Interior.ColorIndex = 0
With Target
'Highlight row and column of the selected cell
.EntireRow.Interior.ColorIndex = 6
.EntireColumn.Interior.ColorIndex = 6
End With

Application.ScreenUpdating = True
End Sub

What this does is follow the cursor which ever cell is selected and it will highlight that row and column to make it easy for the user to see which cell is currently selected. However, I am not able to fill certain cells with different colors and it's because this code seems to refresh the colors of the row and column on the fly while I move the selected cell as I navigate throughout the spreadsheet. Because it keeps refreshing, it will make whatever cell I highlight go back to white.

Is there a way to get it to do what this VBA code is meant to do but also let me highlight certain other cells that I choose?

Thanks for the help.

 

[DanteAmor]

Just tried this for fun, and it's pretty cool, however, in the column reference, the syntax is "COL" not "COLUMN".

That's right, it should be:

=OR(ROW()=CELL("ROW"),COLUMN()=CELL("COL"))

Thanks for the observation.

 

[data808]

Use conditional formatting.
Create a new rule.

On the Home tab, in the Styles group, click Conditional formatting > New Rule… In the New Formatting Rule window, select Use a formula to determine which cells to format. Enter the formula in the corresponding box.

=OR(ROW()=CELL("ROW"),COLUMN()=CELL("COLUMN"))

Click the Format… button to choose your custom format.

Put the following code in the events of your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.ScreenUpdating = False
End Sub

Thank you for the very detailed instructions. I did follow it and unfortunately it doesn't do anything at all. I know I probably missed something but I must say, your instructions were thorough enough for me to not get stuck on any part of the way. So it's odd that it didn't work at all. I think it probably has to do with the section of the instructions where I do the custom format. By default, the format cells window is showing on the Font tab. So I thought that this is not what I wanted and saw the Fill tab. So I went over to that because I figured that this code would be to fill the row and column with the color I chose, which was yellow, as I move the cursor around the spreadsheet to show where my cursor or selected cell was on the spreadsheet at any given time.

The original post where I show the VBA code, does exactly what I want. If I move the cursor to a cell it will highlight the column and row of that cell to pinpoint where my cursor is on the spreadsheet with ease for my eyes to find it quickly. I really like this feature. The only problem I am having is that because it's constantly running and refreshing the spreadsheet as I move the cursor around the spreadsheet and select different cells, it will not allow me to fill any other cells that I want to stay static with that color. Until of course, I decide to unfill it. So basically, I would like for the VBA code to continue highlighting the row and column following the cursor around the spreadsheet with the color yellow but if I decide to fill a particular column for example, with the color blue, I would like it to stay blue until my cursor goes over into that column, then temporarily change it to yellow while I am there, but after the cursor leaves that row/column, go back to its original color which is blue because I manually changed it to that color. Now if I decide to change it back to white, I would like it to do the same. Stay white until my cursor goes into that column or row and when I leave that column or row, go back to its original color which would now be white because that was the color it was before my cursor entered that column or row.

Does that make sense?

 

[data808]

Actually, there are a couple of methods on the board. A quick search is here: highlight active row and column

What I would prefer is combining Conditional Formatting and Auto Calculation by using VBA.
Please note: Although it is possible to select all cells in the worksheet to do this, I would apply this on a limited range if possible.

1. Select the range that you want to make this work (or select all cells).
2. Create a new conditional formatting rule, select Classic, "Use a formula to determine which cells to format", enter the following formula, set the format as you wish, and quit conditional formatting by saving the new rule (the screenshot is from Mac but it is similar on Windows).
   

   =OR(CELL("col")=COLUMN(),CELL("row")=ROW())

   View attachment 101109
3. Now, go to the associated worksheet class in VBA, and instead of your code, use the following to perform a calculation to trigger the conditional formatting:

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       ' Do not calculate if we are in CutCopy mode
       If Application.CutCopyMode = False Then
           Application.Calculate
       End If
   End Sub

This way, you won't have to deal with changing the actual cell colors, and the formatted cells won't lose their original formatting when they are not in the currently active cell's row and column.

Thank you very much for your suggestion. Yours actually does something close to what I want but is very glitchy. At first it seems to work just fine until I select a random group of cells to change the cell color/fill it with a color I choose. Once I do that the following highlights to the cursor gets chopped up and some of it gets left near the cells for which I changed/filled the color manually for. So basically the following highlight is now broken but still following my cursor just not in its full vertical or horizontal lines anymore. If that makes sense.

But pretty close to what I want because the group of cells that I do change the color to, it does stay that color even if my I make the cursor go over it and then off of it, it will still be in its original color that I changed it to.

Thanks for the attempt.

 

[data808]

Actually, there are a couple of methods on the board. A quick search is here: highlight active row and column

What I would prefer is combining Conditional Formatting and Auto Calculation by using VBA.
Please note: Although it is possible to select all cells in the worksheet to do this, I would apply this on a limited range if possible.

1. Select the range that you want to make this work (or select all cells).
2. Create a new conditional formatting rule, select Classic, "Use a formula to determine which cells to format", enter the following formula, set the format as you wish, and quit conditional formatting by saving the new rule (the screenshot is from Mac but it is similar on Windows).
   

   =OR(CELL("col")=COLUMN(),CELL("row")=ROW())

   View attachment 101109
3. Now, go to the associated worksheet class in VBA, and instead of your code, use the following to perform a calculation to trigger the conditional formatting:

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       ' Do not calculate if we are in CutCopy mode
       If Application.CutCopyMode = False Then
           Application.Calculate
       End If
   End Sub

This way, you won't have to deal with changing the actual cell colors, and the formatted cells won't lose their original formatting when they are not in the currently active cell's row and column.

Using your method I got it to work!

Here is what I added to your VBA to make it work with the conditional formatting you suggested. See if it makes sense to you:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    ' Do not calculate if we are in CutCopy mode
    If Target.Cells.Count > 1 Then Exit Sub
    If Application.CutCopyMode = False Then
        Application.Calculate
    End If
End Sub

The On Error Resume Next is there because I would get an overflow error when selecting all cells in the entire spreadsheet. So that made the error go away. I added the If Target.Cells.Count line so that it would ignore when I had multiple cells selected. Usually I would do this so that I can fill that group of cells with another color.

So now it's working as it should. It is a bit glitchy at times where the following highlights gets a bit messed up if I select 2 entire columns side by side and change/fill them with another color, the following highlights will temporarily highlight one of those entire columns but once I move the cursor somewhere on the spreadsheet it will click back into place where it should be. Not sure if you can replicate that but that's what happens for me. If there is a way to get rid of that happening and make it more smooth, I would appreciate it but if not, thank you for helping me figure this out. It will definitely come in handy. I just worry about if I need to print how all this will work? Or if I need to do other unforeseen things with this spreadsheet that this highlighting feature will conflict with something else. I guess I can always come back to update this thread if I find any other issues.

 

[smozgur]

Good to hear it is solved.