VBA Change Value

[rant1]

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!

[TABLE="width: 500"]
<tbody>[TR]
[TD]List in Column A[/TD]
[TD]List in Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alpha[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bravo[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Charlie[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Gamma[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

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.

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]

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]

I'm not sure what you mean. Can you clarify in more detail using an example?