In ThisWorkbook module.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Evaluate("count(search({""H&M"",""CREW HEALTH"",""A&I P&I""},""" & Sh.Name & """))=1") Then
fRow = Application.Match(Target.Value, Sheets("Parameters").Columns(5), 0)
If IsError(fRow) Then
With Sheets("Parameters")
.Range("E" & .Rows.Count).End(xlUp).Offset(1) = Target.Value
.Range("E2", .Range("E" & .Rows.Count).End(xlUp)).Sort .Range("E2"), xlAscending
End With
End If
End If
End Sub
Fontcolor was a remainder of earlier attempts , just had to put entire column E back to Automatic.
Changed your Datavalidation to a Dynamic Range to avoid empty rows at the bottom.(only1 now to allow new entries)
Bookmarks