Simplifying VBA for radio buttons?

[blackwolf8262]

I'm very new to coding and trying to simplify a form to be filled out for work. Here is a screenshot of the cells:

Essentially, when "Yes" is selected, the box will turn green and have "Y", and when "No" is selected, the box is red with "N".
My current VBA code is

Sub OptionButton1_Click()
Range("D4").Interior.ColorIndex = 4
Range("D4") = "Y"
   
End Sub
Sub OptionButton2_Click()
Range("D4").Interior.ColorIndex = 3
Range("D4") = "N"
End Sub
Sub OptionButton3_Click()
Range("D6").Interior.ColorIndex = 4
Range("D6") = "Y"
   
End Sub
Sub OptionButton4_Click()
Range("D6").Interior.ColorIndex = 3
Range("D6") = "N"
End Sub
Sub OptionButton5_Click()
Range("D7").Interior.ColorIndex = 4
Range("D7") = "Y"
   
End Sub
Sub OptionButton6_Click()
Range("D7").Interior.ColorIndex = 3
Range("D7") = "N"
End Sub

Unfortunately this form has about 40 rows, and I feel that copy and pasting these click options will make the code extremely long. I'm wondering if I can simply write a macro where instead of using

Range("D6")

where it's a specific numbered cell, I can make it so that it chooses the cell directly on the right of the cell that contains the radio buttons. I previously tried using

Activecell.Offset(0,1).select

but it would change the value of the cell to the right of a cell I had previously clicked (which could be anywhere on the sheet), and not the cell that was directly to the right of the cell containing the radio buttons.

Any help would be extremely appreciated.

 

[6StringJazzer]

You do not need VBA at all for this.

For each Yes radio button, link it to a cell under the button.
In the cell to the right use the formula

=IF(A1,"Yes","No")

Then use conditional formatting to color them green or red. Here is what it looks like:

And here are the underlying values/formulas:

$scratch.xlsm
	A	B
1	Yes/No
2
3	TRUE	Y
4
5	FALSE	N
6	TRUE	Y
Yes no

Cell Formulas
Range	Formula
B3,B5:B6	B3	=IF(A3,"Y","N")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B:B	Cell Value	="N"	text	NO
B:B	Cell Value	="Y"	text	NO

 

[blackwolf8262]

Hi 6StringJazzer,

Thank you for your reply. Unfortunately I'm not very familiar with conditional formatting - even the VBA code above I just got from a few hours of googling.
Thanks for the tip on not using VBA, it does makes things simpler. I've managed to set it up but I can't get the text to change in B2, it just shows me 1 or 2 depending on if the Yes or No radio buttons are clicked. I can't seem to find an option that allows me to replace the numbers with text.

In cell B2, I used your

=IF(A1,"Yes","No")

formula and it gave me a 1 or 2.
The 2nd screenshot is how I set it up to change colour, but I'm not sure if this is a more convoluted way or if it's right.

Thank you so much for your help already, I really appreciate it. If you happen to know how to solve my original question, I'd love to know (just out of curiosity).

 

[6StringJazzer]

The formula I gave in my post is just a fake example. The real formula has to refer to the actual cells that is linked to the Yes button. I used ActiveX buttons. I think you are using Form buttons. If so your formula needs to change. If your button in row 2 is linked to A2, then your formula must be:

=CHOOSE(A2,"Y","N")

The formula must always refer to the linked cell.

For conditional formatting you need two rules. One turns the cell green if the content is Y, the other turns it red if the content is N.

Here is a link to my actual file that you can inspect to see how it works, but mine has ActiveX buttons. The only difference should be the formulas.

Dropbox

www.dropbox.com