View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Greg Wilson[_4_] Greg Wilson[_4_] is offline
external usenet poster
 
Posts: 218
Default 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)