Perfect! Thanks very much for that! I'm really starting to come around to
macros now.

Am I right in thinking that I might cause problems for this code if I needed
to insert new columns between A & B, so that the cell that needs to
automatically turn to N/A depending on the outcome of A1 is no longer next to
it?

Is there an easy way of adapting the code so that, for example, cells
A1:A100 are the first cells, chosen from a drop-down list, and cells E1:E100
are the ones that need to automatically change to "N/A" if the corresponding
cell in the A-range is "N/A".

Sorry to keep asking so many questions, but since this is my first dabble
with Visual Basic I'm fascinated by it all!

Many thanks again, Neil



"JE McGimpsey" wrote:

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