Named cells in a worksheetchange macro instead of hard cell refere

[Neil Goldwasser]

If anyone could help me here I’d be really grateful!

I have the following code in my worksheet, so that if a cell in column 30,
let's say AD1 has a value of "** N/A **" chosen from the drop-down list, then
the cell to the right in column 31, AE1, automatically becomes "N/A".

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Columns(30))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

However, I have come to realise that if I were to insert a column before AD,
this would mess it all up.
If I could name the cells instead of using hard cell references, so that the
cells in AD are named "Supported_By_2", and the automatically-changing cells
in AE "Support_Type_2", is there a way of adapting the code above?

It would be great if a message box could pop up as well to explain the
reason behind the automatic change, e.g. “Support type 2 has automatically
changed to N/A because a second support plan was not selectedâ€. Is there a
way of doing this?

----------------------------------------------
Ideally, it would be really, really useful to have two or three of these
functions described above. I tried inserting another section of code as well
as the first, as shown

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Columns(25))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

I had hoped that this would turn Z-column cells into "N/A" if the
corresponding Y-column cell was "** N/A **", but when I tried it I got an
error message in the Visual Basic Editor saying
"Compile error:
Ambiguos name detected: Worksheet_Change"

I am sure it is an embarrassingly simple answer, but what do I need to do in
order to have more than one of these functions running in the same worksheet?

On behalf of our many students who will benefit from the new database, I
thank all those in advance who may be able to help me!
Neil Goldwasser

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Range("Supported_By_2"))
If Not rTarget Is Nothing Then
For Each rCell In rTarget
If rCell.Value = "** N/A **" Then
Application.EnableEvents = False
rCell.Offset(0, 1).Value = "N/A
Msgbox "Cell " & rCell.Offset(0, 1).address(False,False)
& _
" has automatically changed to N/A because
a " & _
"second support plan was not selected"
Application.EnableEvents = True
End If
Next rCell
End If
End Sub

this may get annoying witrh all those popups.

Replace the exsisting change proc, don't add this as a new.

--

HTH

RP
(remove nothere from the email address if mailing direct)