fixing macro to highlight a row if a checkbox is checked

[Guest]

I've inherited a workbook with the request to put a function in the "totals" tab, which was removed some time between the original version and the one I received. The function highlights the row accompanying a checkbox if the box is checked (basically just allowing the person who uses the workbook to keep better visual track of which of the 20 sections of the workbook she has completed). I've re-created the columns, checkboxes, etc., and copied the macro from the original workbook. The function kind of works now - when you click the checkbox, the row does turn yellow, but you also get messages for all the rows, and when you uncheck the checkbox the highlighting doesn't go away. I have zero experience with macros, so I'm surprised I've gotten even that far. Below is the code for the macro, which is pretty much Greek to me. Any assistance would be much appreciated. (And let me know if I've omitted any important info.

******
Sub HighlightRow(

' HighlightRow Macr
' Macro recorded 6/20/2003 by Lisa Moor

Application.ScreenUpdating = Fals

Range("A29").Selec
If ActiveCell.Value > 0 The
MsgBox "A29 not zero

ActiveSheet.Unprotec

For i = 9 To 2
Range("A" & i).Selec
If ActiveCell.Value = 1 The
MsgBox "i is 1

Range("E" & i & ":AE" & i).Selec

With Selection.Interio
.ColorIndex = 1
.Pattern = xlSoli
.PatternColorIndex = xlAutomati
End Wit

Els

MsgBox "i is 0

Range("E" & i & ":AD" & i).Selec

With Selection.Interio
.ColorIndex = xlNon
.Pattern = xlSoli
.PatternColorIndex = xlAutomati
End Wit


End I

Next

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=Tru

Application.ScreenUpdating = Tru

End I

End Su

 

[Greg Wilson]

As far as I can tell this is what you're looking for. I
am assuming the check boxes are all from the Forms tool
bar. The first macro "AssignMacro" will automactically
assign the "HighlightRow2" macro to ALL check boxes on the
active sheet. It is intended to only be run once. Make
sure that this is what you want. It is not expected that
you will need this macro again after doing this.

The second macro "HighlightRow2" will do what I think you
want. Please advise if I've misunderstood something.

Warning: Ensure that you have a backup copy of your
workbook before using it. You cannot undo a macro unlike
manual changes to a workbook.

'Use to assign second macro to all check boxes
Sub AssignMacro()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
CB.OnAction = "HighlightRow2"
Next
End Sub

Sub HighlightRow2()
Dim Rng As Range
Dim Rw As Integer
Dim CB As CheckBox

Set CB = ActiveSheet.CheckBoxes(Application.Caller)
Rw = CB.TopLeftCell.Row
Set Rng = Range("E" & Rw & ":AE" & Rw)
Application.ScreenUpdating = False
With ActiveSheet
.Unprotect
If CB.Value = 1 Then
Rng.Interior.ColorIndex = 19
Else
Rng.Interior.ColorIndex = xlNone
End If
.Protect
End With
Application.ScreenUpdating = True
End Sub

Regards,
Greg
(VBA amateur)