Macro: highlighting row and column active

[ed.ayers315]

Hi Folks,

I have a macro to highlight the row and column that the cursor is in.

3 Problems I have:

1) I needed a way to turn it off; so added the code marked #2 to each sheet so a doulble click would turn the highlighting off. The trouble with this is that it places the cell in edit mode that was doulble clicked. Not what I need.

2) Would there be code to ignore merged cells; in columns and rows?

3) I want to turn of the highlights before printing. Without the current cell being put in edit mode.

Code #1 This is on the Workbook

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Integer
Cells.Interior.ColorIndex = x1none
For i = 1 To Target.Row
Cells(i, Target.Column).Interior.ColorIndex = 6
Next

For i = 1 To Target.Column
Cells(Target.Row, i).Interior.ColorIndex = 6
Next
End Sub

This is on the worksheet
Code #2

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Integer
Cells.Interior.ColorIndex = x1none
For i = 1 To Target.Row
Cells(i, Target.Column).Interior.ColorIndex = x1none
Next

For i = 1 To Target.Column
Cells(Target.Row, i).Interior.ColorIndex = x1none
Next
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
End Sub

 

[lrobbo314]

This ignores merged cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer
Cells.Interior.ColorIndex = x1none
For i = 1 To Target.Row
If Cells(i, Target.Column).MergeCells = False Then
Cells(i, Target.Column).Interior.ColorIndex = 6
End If
Next
For i = 1 To Target.Column
If Cells(i, Target.Row).MergeCells = False Then
Cells(Target.Row, i).Interior.ColorIndex = 6
End If
Next
End Sub

and this will clear the background, but for all cells in all worksheets before printing

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each ws In ActiveWorkbook
    ws.Cells.Interior.ColorIndex = xlNone
Next ws
End Sub

Or I suppose you could replace that with the code you have for the double click event so it only deals with the active row and column.

 

[ed.ayers315]

Thanks Irobbo314,

Works great

Take care!

 

[theBardd]

This should stop the doubleclick being in edit mode

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Integer
    
    Me.Cells(1, Target.Column).Resize(Target.Row).Interior.ColorIndex = xlColorindexNone
    Me.Cells(Target.Row, 1).Resize(, Target.Column).Interior.ColorIndex = xlColorindexNone
    
    Cancel = True
End Sub