I currently have a data validation drop down list incolumns F,G,J and R. If an option in 1 or any of those four columns is selectedand contains “(1)” within the text, I want the cell in column S toautomatically populate with the same data from the cell in column T.<o></o>
This has to VBA nota formula. I currently have a formula in Column S that the above VBA will needto override if “(1)” is found in F,G,J orR. If “(1)” is not found I want theVBA code to do nothing. But if a cell is changed from text containing “(1)” totext that is not containing it, I would like it to re-insert my orginal formula Ihad in column S.
The code is what I have so far, but its changing column S regardless of what is in Column R simply because of the change event. Im not sure how to specify that only contains text with (1). I also tried making my range a union and it does not register changes made in columns F,G, or J. PLEASE HELP!!!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("R:R")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Copies the values entered into column B into C
Target.Offset(, 1).FormulaR1C1 = "=RC[1]"
End Sub
This has to VBA nota formula. I currently have a formula in Column S that the above VBA will needto override if “(1)” is found in F,G,J orR. If “(1)” is not found I want theVBA code to do nothing. But if a cell is changed from text containing “(1)” totext that is not containing it, I would like it to re-insert my orginal formula Ihad in column S.
The code is what I have so far, but its changing column S regardless of what is in Column R simply because of the change event. Im not sure how to specify that only contains text with (1). I also tried making my range a union and it does not register changes made in columns F,G, or J. PLEASE HELP!!!
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("R:R")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Copies the values entered into column B into C
Target.Offset(, 1).FormulaR1C1 = "=RC[1]"
End Sub