Hi Howard,

Am Fri, 6 Feb 2015 03:05:39 -0800 (PST) schrieb L. Howard:

Abilene, TX (ABI)
Abilene, TX (ABI)
Adak Island, AK (ADK)
Akiachak, AK (KKI)
Akiak, AK (AKI)
Akron/Canton, OH (CAK)
Akuton, AK (KQA)
Alakanuk, AK (AUK)
Alamogordo, NM (ALM)X
Alamosa, CO (ALS)

try it with autofilter. This way is more reliable and faster:

Sub AP_by_State()
Dim LRow As Long

With Sheets("State AP")
LRow = .Cells(Rows.Count, "C").End(xlUp).Row
.Columns("C").AutoFilter Field:=1, _
Criteria1:="*, " & .Range("B1") & "*"
.Range("C2:C" & LRow).Copy .Range("F1")
.Range("C2:C" & LRow).SpecialCells(xlCellTypeVisible) _
.Interior.ColorIndex = 19
.AutoFilterMode = False
End With
End Sub

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "B1" Or Target.Count 1 Then Exit Sub

Range("C:C").Interior.ColorIndex = xlNone
Range("F:F").ClearContents

Call AP_by_State

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional