Dear All

I have writtent some code but I belive I have done it the long way and think
there is a much simpler way of doing things. I am trying to compare two
ranges in a case statement. Below is the code, can anyone help with reducing
the code required or a more efficient way of doing it

Tahnsk

Regards

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngData As Range, rngCell As Range
Dim Wday1data As Range, Wday1Cell As Range
Dim icolor As Integer
Dim WrkDays As Date
Dim StartDate As Date
Dim Enddate As Date
Dim Day1 As Integer

Dim icolor2 As Integer
Dim icolor3 As Integer
Dim icolor4 As Integer
Dim icolor5 As Integer
Dim icolor6 As Integer
Dim icolor7 As Integer
Dim icolor8 As Integer
Dim icolor9 As Integer
Dim icolor10 As Integer
Dim icolor11 As Integer

icolor2 = 43
icolor3 = 13
icolor4 = 39
icolor5 = 36
icolor6 = 45
icolor7 = 33
icolor8 = 22
icolor9 = 35
icolor10 = 23
icolor11 = 43

'Dim Wday1data As Range, Wday1Cell As Range

StartDate = "01/01/2010"
'DateAdd("D", -1# * DatePart("D", Today) + 1, Today)
Enddate = WorksheetFunction.WorkDay(StartDate, Day1)

With ThisWorkbook.Worksheets("Calendar")
Set rngData = .Range("B5:X55")
Set Wday1data = .Range("AE5:AP5")

End With
'define the data range to evaluate

For Each rngCell In rngData
Select Case rngCell.Value


Case Worksheets("Calendar").Range("AE2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("AE3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("AE4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("AE5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("AE6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("AE7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("AE8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("AE9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("AE10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("AE11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Af2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Af3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Af4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("AF5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("AF6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("AF7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("AF8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("AF9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("AF10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("AF11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Ag2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Ag3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Ag4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("ag5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("ag6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("ag7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("ag8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("ag9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("ag10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("ag11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Ah2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Ah3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Ah4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("ah5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("ah6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("ah7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("ah8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("ah9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("ah10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("ah11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Ai2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Ai3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Ai4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("ai5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("ai6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("ai7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("ai8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("ai9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("ai10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("ai11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Aj2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Aj3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Aj4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("aj5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("aj6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("aj7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("aj8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("aj9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("aj10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("aj11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Ak2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Ak3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Ak4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("ak5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("ak6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("ak7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("ak8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("ak9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("ak10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("ak11").Value: icolor = icolor11



Case Worksheets("Calendar").Range("Al2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Al3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Al4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("al5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("al6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("al7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("al8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("al9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("al10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("al11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Am2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Am3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Am4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("am5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("am6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("am7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("am8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("am9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("am10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("am11").Value: icolor = icolor11



Case Worksheets("Calendar").Range("An2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("An3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("An4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("an5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("an6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("an7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("an8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("an9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("an10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("an11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Ao2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Ao3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Ao4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("ao5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("ao6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("ao7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("ao8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("ao9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("ao10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("ao11").Value: icolor = icolor11


Case Worksheets("Calendar").Range("Ap2").Value: icolor = icolor2
Case Worksheets("Calendar").Range("Ap3").Value: icolor = icolor3
Case Worksheets("Calendar").Range("Ap4").Value: icolor = icolor4
Case Worksheets("Calendar").Range("ap5").Value: icolor = icolor5
Case Worksheets("Calendar").Range("ap6").Value: icolor = icolor6
Case Worksheets("Calendar").Range("ap7").Value: icolor = icolor7
Case Worksheets("Calendar").Range("ap8").Value: icolor = icolor8
Case Worksheets("Calendar").Range("ap9").Value: icolor = icolor9
Case Worksheets("Calendar").Range("ap10").Value: icolor = icolor10
Case Worksheets("Calendar").Range("ap11").Value: icolor = icolor11

Case Else: icolor = xlColorIndexNone
End Select
rngCell.Interior.ColorIndex = icolor
Next rngCell
End Sub