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]

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

 

[smozgur]

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())

   
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.

 

[smozgur]

Use conditional formatting.

You are faster as usual, @DanteAmor!

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

I like this approach more than the method I used, @DanteAmor.
Since the ScreenUpdating property resets back to True after selection change event completed... Yes, I like this.

 

[DanteAmor]

You are faster as usual

Thanks @smozgur.

I also used Calculate, but in some versions, it did not allow undo, trying other approaches, the best result was Application.ScreenUpdating = False

 

[dreid1011]

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

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

 

[dreid1011]

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.

You mention limiting this to a specific area by only selecting the desired area, however, I find that it still highlights the row or column in the area if I select a cell outside of the area. Would setting limits within the SelectionChange code help?

 

[smozgur]

You mention limiting this to a specific area by only selecting the desired area, however, I find that it still highlights the row or column in the area if I select a cell outside of the area. Would setting limits within the SelectionChange code help?

It works as I explained in my computer. It only affects the selected range, and also only triggered by the cells in that range. (Remember to delete the previous conditional formatting?)
There is nothing to do with SelectionChange. It is only used as a helper event to refresh the view.

 

[dreid1011]

It works as I explained in my computer. It only affects the selected range, and also only triggered by the cells in that range. (Remember to delete the previous conditional formatting?)
There is nothing to do with SelectionChange. It is only used as a helper event to refresh the view.

I'm not the OP, just playing with this because it looked cool. Anyway, I set my CF to apply to A1:Z26 but when I select AA26, it still highlights row 26 from columns A to Z. Doesn't matter what I select outside of the range, it still highlights something.

Just thought you might have a theory on why.

 

[smozgur]

Just thought you might have a theory on why.

You are right. It makes sense actually, when ROW() or COLUMN() matches, it triggers the rule. Apparently not that perfect method if we limit to range.

We can still make it work with an extra AND condition. It is not as perfect as before but kind of a workaround, maybe?
In my opinion, still a better approach than setting the interior color in the SelectionChange event procedure.
For $A$1:$Z$26:

=AND(OR(CELL("col")=COLUMN(),CELL("row")=ROW()),CELL("row")<27, CELL("col")<27)