Private Sub Worksheet_Change(ByVal Target As Range)
Dim iInterior As Long, iFont As Integer
Dim rng As Range, c As Range
Dim a, b, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, a1, b1, c1, d1, e1, f1, g1 As String
Set rng = Intersect(Target, Range("J2:J500"))
a = Sheets("Allocation").Range("L5").Value
b = Sheets("Allocation").Range("L6").Value
d = Sheets("Allocation").Range("L7").Value
e = Sheets("Allocation").Range("L8").Value
f = Sheets("Allocation").Range("L9").Value
g = Sheets("Allocation").Range("L10").Value
h = Sheets("Allocation").Range("L11").Value
i = Sheets("Allocation").Range("L12").Value
j = Sheets("Allocation").Range("L13").Value
k = Sheets("Allocation").Range("L14").Value
l = Sheets("Allocation").Range("L15").Value
m = Sheets("Allocation").Range("L16").Value
n = Sheets("Allocation").Range("L17").Value
o = Sheets("Allocation").Range("L18").Value
p = Sheets("Allocation").Range("L19").Value
q = Sheets("Allocation").Range("L20").Value
r = Sheets("Allocation").Range("L21").Value
s = Sheets("Allocation").Range("L22").Value
t = Sheets("Allocation").Range("L23").Value
u = Sheets("Allocation").Range("L24").Value
v = Sheets("Allocation").Range("L25").Value
w = Sheets("Allocation").Range("L26").Value
x = Sheets("Allocation").Range("L27").Value
y = Sheets("Allocation").Range("L28").Value
z = Sheets("Allocation").Range("L29").Value
a1 = Sheets("Allocation").Range("L30").Value
b1 = Sheets("Allocation").Range("L31").Value
c1 = Sheets("Allocation").Range("L32").Value
d1 = Sheets("Allocation").Range("L33").Value
e1 = Sheets("Allocation").Range("L34").Value
f1 = Sheets("Allocation").Range("L35").Value
g1 = Sheets("Allocation").Range("L36").Value
If Not rng Is Nothing Then
For Each c In rng
Select Case LCase$(c.Value)
Case f
iInterior = RGB(128, 128, 0)
iFont = 1
Case i
iInterior = RGB(153, 204, 255)
iFont = 1
Case z
iInterior = RGB(51, 204, 204)
iFont = 1
Case s
iInterior = RGB(153, 255, 153)
iFont = 1
Case j
iInterior = RGB(0, 128, 0)
iFont = 1
Case k
iInterior = RGB(255, 153, 204)
iFont = 1
Case q
iInterior = RGB(0, 255, 0)
iFont = 1
Case w
iInterior = RGB(255, 0, 255)
iFont = 1
Case u
iInterior = RGB(0, 204, 255)
iFont = 1
Case v
iInterior = RGB(247, 150, 70)
iFont = 1
Case r
iInterior = RGB(0, 255, 255)
iFont = 1
Case a
iInterior = RGB(153, 51, 102)
iFont = 1
Case m
iInterior = RGB(153, 204, 0)
iFont = 1
Case g
iInterior = RGB(150, 150, 150)
iFont = 1
Case d
iInterior = RGB(217, 217, 217)
iFont = 1
Case b
iInterior = RGB(255, 204, 0)
iFont = 1
Case n
iInterior = RGB(255, 255, 0)
iFont = 1
Case t
iInterior = RGB(51, 153, 102)
iFont = 1
Case p
iInterior = RGB(204, 255, 255)
iFont = 1
Case b1
iInterior = RGB(255, 204, 153)
iFont = 1
Case o
iInterior = RGB(204, 153, 255)
iFont = 1
Case c1
iInterior = RGB(255, 0, 102)
iFont = 1
Case a1
iInterior = RGB(204, 255, 51)
iFont = 1
Case y
iInterior = RGB(255, 128, 128)
iFont = 1
Case d1
iInterior = RGB(198, 89, 17)
iFont = 1
Case h
iInterior = RGB(235, 230, 153)
iFont = 1
Case x
iInterior = RGB(47, 117, 181)
iFont = 1
Case e
iInterior = RGB(191, 143, 0)
iFont = 1
Case l
iInterior = RGB(169, 208, 142)
iFont = 1
Case e1
iInterior = RGB(169, 208, 142)
iFont = 1
Case f1
iInterior = RGB(169, 208, 142)
iFont = 1
Case g1
iInterior = RGB(169, 208, 142)
iFont = 1
Case ""
iInterior = RGB(0, 0, 0)
iFont = 1
Case Else
iInterior = RGB(153, 51, 0)
iFont = 1
End Select
With c(1, -8).Resize(, 14)
If iInterior > 0 And iFont > 0 Then
.Interior.Color = iInterior
.Font.ColorIndex = iFont
Else
.Interior.ColorIndex = xlNone
.Font.Color = 0
End If
End With
'reset colors for next cell in the loop
iInterior = Empty
iFont = Empty
Next c
End If
Bookmarks