Need help applying my current one row macro to other rows

[oucam]

I've got a VERY basic macro that only applies to one row right now and I need to copy it to other rows so that it will apply to the specific row it is in.

Here is what I have right now:

Private Sub OptionButton1_Click()
Range("A3:L3").Select
Range("L3").Activate
Selection.Interior.ColorIndex = xlNone

End Sub
Private Sub OptionButton2_Click()
Range("A3:L3").Select
Range("L3").Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End Sub

All I am needing the macro to do is highlight the row yellow when OptionButton2 is selected and unhighlight when OptionButton1 is clicked. I am able to do this in the original row that I have the macro but I don't know how to get it to transfer to the other rows in the sheet. I also need the flexibility to be able to add rows in case there is more info in some sheets and have the macro work in those rows.

Thanks!

 

[jasonb75]

Welcome to the board!

This revised version or your code should do it, click a cell in the row you want to change, then click the button.

Private Sub OptionButton1_Click()
Range("A" & ActiveCell.Row & ":L" & ActiveCell.Row).Interior.ColorIndex = xlNone
End Sub
Private Sub OptionButton2_Click()
Range("A" & ActiveCell.Row & ":L" & ActiveCell.Row).Interior.ColorIndex = 6
End Sub

Hope this helps

JB

 

[Yard]

You mean you want to colour the rows which are currently selected, from Column A to Column L?

Private Sub OptionButton2_Click()

lngRowStart = Selection.Row
lngRowEnd = Selection.Rows.Count + lngRowStart - 1

Set rngTemp = Range(Cells(lngRowStart, "A"), Cells(lngRowEnd, "L"))

With rngTemp.Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With

End Sub



Private Sub OptionButton1_Click()

lngRowStart = Selection.Row
lngRowEnd = Selection.Rows.Count + lngRowStart - 1

Set rngTemp = Range(Cells(lngRowStart, "A"), Cells(lngRowEnd, "L"))

With rngTemp.Interior
    .ColorIndex = xlNone
End With

End Sub

 

[oucam]

First off, thanks for the help!

Those are almost what I need. I have the option buttons in a cell in the row and want the option buttons ("yes" and "no") to be in the equivalent cell in each row. Then when I click "no" (OptionButton2), the row turns yellow. When I click "yes" (OptionButton1), the row turns white again. Is there a way to get these option buttons to copy down the same column so that they can apply to the respective row that they are in? Sorry if that's confusing.

Thanks,

Cameron

 

[jasonb75]

Personally I would avoid the macro and use conditional format with a Checkbox, not optionbox linked to column M of the same row.

Conditional format Applies to $A:$M
Formula =$M1
Format for yellow fill
This would turn the row yellow when checked, or leave white if unchecked.

 

[Yard]

I would agree. Apart from the hard work involved in setting up all those option boxes, you'd need to have code for each single one to react when it was clicked.

jasonb75's solution is much tidier.

You could achieve the same thing using Data Validation, with a dropdown box for "Yellow" and "White".

 

[jasonb75]

Good one Yard, I didn't think of using a simple dropdown, 5 second setup with data validation.

You could also set the dropdown to yes / no so it's the same as your optionbox setup.

I would agree. Apart from the hard work involved in setting up all those option boxes, you'd need to have code for each single one to react when it was clicked.

jasonb75's solution is much tidier.

You could achieve the same thing using Data Validation, with a dropdown box for "Yellow" and "White".

 

[oucam]

Oh wow, that works much better, thanks for the quick help!