Need help applying my current one row macro to other rows

oucam

New Member
Joined
Mar 16, 2009
Messages
5
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!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,885
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
You mean you want to colour the rows which are currently selected, from Column A to Column L?

Code:
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

New Member
Joined
Mar 16, 2009
Messages
5
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,885
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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

Well-known Member
Joined
Dec 30, 2008
Messages
11,885
Office Version
  1. 365
Platform
  1. Windows
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".
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top