rant1

New Member
Joined
Jan 11, 2018
Messages
2
Hello,

I am working with a spreadsheet with numerous validated lists from named ranges; I'm good at the beyond-data-entry functions of Excel, but a novice a VBA. I've googled a ton, and I think I have the gist of how the code I'm looking for should work, but no idea on writing it or defining the parameters.

I have some simplistic example data below; all the entries in each drop down list would be valid, so I don't want to create dependent lists.

I'm trying to control certain combinations of selections in two columns. I don't want a user to be able to select "1" in column A and "Beta" in column B. My ideal scenario would be if that occurred, for a SheetChange or BeforeSave event change column B from "Beta" to "Bravo". From what I've seen in this forum and others, most people are only trying to clear a cell based on the value of another, not have the code compare the values and replace one.

If that's not possible, I'd take clearing out Column B, preferably with an error message calling out " 'Beta' is not acceptable with '1' ".

Thanks in advance for any help!

List in Column AList in Column B
1Alpha
2Beta
3Bravo
Charlie
Gamma

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,097
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make your selections in columns A and B. Please note that the macro works only when "1" is selected in column A and "Beta" is selected in column B as per your example.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:B")) Is Nothing Then Exit Sub
    If Target.Column = 1 Then
        If Target = 1 And Target.Offset(0, 1) = "Beta" Then
            Target.ClearContents
            MsgBox ("You cannot select '1' with 'Beta' exisiting in column B.")
        End If
    ElseIf Target.Column = 2 Then
        If Target = "Beta" And Target.Offset(0, -1) = "1" Then
            Target.ClearContents
            MsgBox ("You cannot select 'Beta' with '1' exisiting in column A.")
        End If
    End If
End Sub
 

rant1

New Member
Joined
Jan 11, 2018
Messages
2
Thanks! Is there a way that it would always clear "Beta" from column B, even if "1" in column A was the second value entered?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,097
I'm not sure what you mean. Can you clarify in more detail using an example?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

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