Remember that you can have only one worksheet change function per worksheet when you're coding. Neither will work right if you have more than one if any of the code for the sheet works at all.
You also used the same statement over and over. Try to think of ways to combine your code so it is smaller. doing so makes it not only easier to read but usually faster executing as well.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FocusRange As Range, Cell As Range, iCellColor As Long, iFontColor As Long
Set FocusRange = Intersect(Target, [N2:N1000]) 'Formatting only applies to cells N2:N1000
If Not FocusRange Is Nothing Then
Target.Parent.Unprotect Password:="brighton"
For Each Cell In FocusRange.Cells
iFontColor = xlAutomatic
'Why Was there a case for null string? What if the text in cell is "XYZ" for instance? Changed "" below to Case Else
Select Case UCase(Cell.Text) 'Changed to upper case so AG, ag, Ag, aG would all be the 'same'
Case "AB"
iCellColor = 33 'Sky Bue highlighting
Case "AG"
iCellColor = 4 'Bright Green highlighting
Case "BB"
iCellColor = 44 'Lt. Orange highlighting
Case "CH"
iCellColor = 8 'Turquoise highlighting
Case "CR"
iCellColor = 6 'Lt. Orange highlighting
Case "HG"
iCellColor = 39 'Lavender highlighting
Case "JH"
iCellColor = 41 'Lt. Blue highlighting
Case "LB"
iCellColor = 42 'Turquoise highlighting
Case "MD"
iCellColor = 37 'Pale Blue highlighting
Case "MH"
iCellColor = 46 'Orange highlighting
Case "NG"
iCellColor = 7 'Pink highlighting
Case "NS"
iCellColor = 14 'Teal highlighting
Case "RG"
iCellColor = 50 'Sea Green highlighting
Case "RJ"
iCellColor = 34 'Lt. Turquoise highlighting
Case "RL"
iCellColor = 54 'Plum highlighting
iFontColor = 2 'White Text
Case "RO"
iCellColor = 45 'Lt. Orange highlighting
Case "RR"
iCellColor = 15 'Grey - 25% highlighting
Case "SR"
iCellColor = 55 'Indigo highlighting
iFontColor = 2 'White Text
Case "ST"
iCellColor = 13 'Violet highlighting
iFontColor = 2 'White Text
Case "TA"
iCellColor = 23 'Ocean Blue highlighting
iFontColor = 2 'White Text
Case "TN"
iCellColor = 24 'Ice Blue highlighting
Case Else
iCellColor = 2 'White highlighting
End Select
Cell.Interior.ColorIndex = iCellColor
Cell.Font.ColorIndex = iFontColor
'This is the other change function you had, they both require column N so contained it inside the same if/then.
' If .Count = 1 Then 'only dating if 1 cell changes? Remarked so dates all changed cells
With Cells(Cell.Row, 19)
.NumberFormat = "DD/MM/YYYY"
.Value = Date
End With
' End If 'only dating if 1 cell changes? Remarked so dates all changed cells
'End of the code used to do the 2nd part of what you wanted done.
Next Cell
Target.Parent.Protect Password:="brighton"
End If
'End With 'There is no With statement for this to end
End Sub
Hopes this helps for what you need and gives you some insight for future coding.
Multiple "Worksheet_Change" routines
