I stole the bulk of the following code from one of Dave Peterson's many
helpful postings on this site. I have  a number of named lists on the sheet
called "Feed Data." The lists are named "list1," list2," list3," etc. Each
one is defined with an OFFSET function.

Data Validation in various columns on the main sheet refer to these lists
for their dropdowns. (The DV is set up to accept non-list entries after
showing a warning box.). The macro that follows allows users to have new,
non-source list entries to be added onto the original list so that it
subsequently appears in the dropdowns. (Again, there are several lists
(seven), each one used as a DV list for a different column on the main sheet
(Column A, Column B, etc.)) In adapting it for my workbook, however, I
clearly did someting wrong, because it works great--but only for one cell in
each column (a1, b1, c1, etc.)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myList As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1,b1,c1,d1,e1,f1,g1")) Is Nothing Then Exit
Sub
If Target.Value = "" Then Exit Sub

Set myList = Nothing
Select Case LCase(Target.Address(0, 0))
Case Is = "a1"
Set myList = Me.Parent.Worksheets("Feed Data").Range("list1")
Case Is = "b1"
Set myList = Me.Parent.Worksheets("Feed Data").Range("list2")
Case Is = "c1"
Set myList = Me.Parent.Worksheets("Feed Data").Range("list3")
Case Is = "d1"
Set myList = Me.Parent.Worksheets("Feed Data").Range("list4")
Case Is = "e1"
Set myList = Me.Parent.Worksheets("Feed Data").Range("list5")
Case Is = "f1"
Set myList = Me.Parent.Worksheets("Feed Data").Range("list6")
Case Is = "g1"
Set myList = Me.Parent.Worksheets("Feed Data").Range("list7")
'etc
End Select

If myList Is Nothing Then
Exit Sub
End If

If IsNumeric(Application.Match(Target.Value, myList, 0)) Then
'already there, do nothing
Else
With myList
.Cells(.Cells.Count).Offset(1, 0).Value = Target.Value
Set myList = .Resize(.Rows.Count + 1, 1)
End With

With myList
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With
End If

End Sub

Again, the macro as it currently stands seems to work only for single-cell
ranges: a1, b1, c1, d1, etc. I need it to apply to a range of cells in each
column, though, so that (for example), if I use the DV dropdown in cell A2
and want to add an item not in List1, than the item will be added to List1.
I've tried modifiying the code like this:

...

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1:a500,b1,c1,d1,e1,f1,g1")) Is Nothing Then
Exit Sub
If Target.Value = "" Then Exit Sub

Set myList = Nothing
Select Case LCase(Target.Address(0, 0))
Case Is = "a1:a500"
Set myList = Me.Parent.Worksheets("Feed Data").Range("list1")

...

I figured that cells A1:A500 would be part of the change event, but nothing
happens. Does any of this make sense? What am I doing wrong? Help!