There are many ways, here's one:

For column A:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rTarget As Range
Dim rCell As Range
Set rTarget = Intersect(Target, Columns(1))
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

For A1:A100:

Change

Columns(1)

to

Range("A1:A100")



In article ,
Neil Goldwasser <Neil wrote:

Thanks for that - that was great! Just one more question if I may though (I
apologise if the answer is a simple one, I am very much a macro novice!)

How could I adapt this code so that it works not just for cell A1, but for a
range of cells. What would I need to change for this function to work for all
of column A, or for cells A1 to A100, for example.

Many thanks, Neil


"JE McGimpsey" wrote:

I think the only way you can do this is with an event macro, since once
you choose something from the validation menu, any formula will be
overwritten.

Put this in your worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("A1").Value = "N/A" Then
Application.EnableEvents = False
Range("B1").Value = "N/A"
Application.EnableEvents = True
End If
End If
End Sub