Automatically Change String Value of a Cell Based on the String Value of Another Cell

[nryan]

Hi Forum,

I've had a tough time searching for a solution with Google. I don't quite know how else to phrase it.

I have an Excel workbook where users input data. In two of the columns, D and E, users select from in-cell dropdowns (data validation). Each column has three selections to choose from. When "Dual" is selected in column D, the selection for column E should always be "n/a". The problem is users have consistently been selecting "Vertical" in column E. There are thousands of rows of data and many of them have this mistake.

Since "Dual" and "n/a" always go together I want to make it so that when "Dual" is selected in column D, "n/a" is automatically put into column E. Here's what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ItemSelection As Range
Dim AngleOrVert As Range


Set ItemSelection = Intersect(Range("D2:D100000"), Target)
Set AngleOrVert = Intersect(Range("E2:E100000"), Target)


If ItemSelection Is "Dual" Then
    AngleOrVert.Value = "n/a"
End If

End Sub

This gets me a Type mismatch error. I'd like to get this to work and if anyone knows how to do this with conditional formatting or data validation rather than a macro I'm fine with that. If you know how I can change all the wrong entries that are already in the worksheet without going through it line by line that would be nice too.

Thanks for your time.
-Nick

 

[LloydFinancials]

You are close.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ItemSelection As Range
Dim AngleOrVert As Range


Set ItemSelection = Intersect(Range("D2:D100000"), Target)
If Target = "Dual" Then
    Cells(Target.Row, 5).Value = "n/a"
End If


End Sub

See if this works.

 

[nryan]

Thanks for the response man. That got rid of the error, but nothing happens when "Dual" is selected in column D. Column E stays blank. I tried eliminating the data validation for column E to see if that was getting in that way, but still nothing. Should the variable "AngleOrVert" be put somewhere? In you code it's just there, dimensioned as a range, but it's not being used.

 

[LloydFinancials]

Ok. This is my sheet setup:

Excel 2010
	D	E	I	J	K	L
1	Dual	n/a			Dual	n/a
2	Dual	n/a			Single	1
3	Dual	n/a
Sheet1

There are dropdowns in D and E and the lists for the dropdowns are in K and L. For this, the code works. I select "Dual" and E autofills with "n/a". So I am not sure what is happening. The AngleOrVert line can be deleted.