Thread: Excel row highlighter with on/off option

I've used a pre-existing vba script with some slight modifications to highlight the row of an active cell.
I'm trying to have the user be able to turn it off and on. Right now it works fine except when you disable the highlight feature and then close the workbook. The result is a whole row which stays highlighted. Not entirely sure how to fix this. Any suggestions?

Here is my modified code. It is originally from http://www.mcgimpsey.com/excel/highlightrow.html
except with the added feature of checking a checkbox.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Worksheets("Menu").CheckBox1.Value = True Then
Const cnNUMCOLS As Long = 40 ' This is the number of columns which are highlighted
Const cnHIGHLIGHTCOLOR As Long = 36 'default lt. yellow
Static rOld As Range
Static nColorIndices(1 To cnNUMCOLS) As Long
Dim i As Long
If Not rOld Is Nothing Then 'Restore color indices
With rOld.Cells
If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
For i = 1 To cnNUMCOLS
.Item(i).Interior.Color = nColorIndices(i)
Next i
End With
End If
Set rOld = Cells(ActiveCell.Row, 1).Resize(1, cnNUMCOLS)
With rOld
For i = 1 To cnNUMCOLS
nColorIndices(i) = .Item(i).Interior.Color
Next i
.Interior.ColorIndex = cnHIGHLIGHTCOLOR
End With


ElseIf Worksheets("Menu").CheckBox1.Value = False Then

If Not rOld Is Nothing Then 'Restore color indices
With rOld.Cells
If .Row = ActiveCell.Row Then Exit Sub 'same row, don't restore
For i = 1 To cnNUMCOLS
.Item(i).Interior.Color = nColorIndices(i)
Next i
End With
End If
End If


End Sub

another way of doing is

view-toolbars-form
in the form toolbars click CHECKBOX and
move the cursor to the sheet to draw a rectangle and check box will be embedded and the name of the
check box will be checkbox1 .

now in a module copy this code

Sub checkboxes()
ActiveSheet.Cells.Interior.ColorIndex = xlNone
 If ActiveSheet.checkboxes("Check Box 1").Value = 1 Then
 Selection.EntireRow.Interior.ColorIndex = 6
 End If
 
End Sub

now go the sheet rightlclick the checkbox and click "assign macro" and choose this macro "checkboxes" for assining
select a cell outside the checkbox area

if the check box is having tick
now select any cell outside the check box
see what happens

now again click the check box tick will go off
now see what happens.

will this help you???????????????