fixing macro to highlight a row if a checkbox is checked
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)
|