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

I don't really know what you are trying to do, and somehow suspect what you
are doing isn't quite it. Eg, I doubt you want all that code to run whenever
some random cell on the sheet changes (you don't refer to Target at all).
However, hopefully the following should replicate your code more efficiently
(but I haven't tested so double check)

Option Explicit
Private mArrClrIdx(0 To 11) As Long

Sub PopClrArray()
PopClrArray(0) = -1 ' to show the array has been populated
mArrClrIdx(2) = 43 ' icolor2 etc
mArrClrIdx(3) = 13
mArrClrIdx(4) = 39
mArrClrIdx(5) = 36
mArrClrIdx(6) = 45
mArrClrIdx(7) = 33
mArrClrIdx(8) = 22
mArrClrIdx(9) = 35
mArrClrIdx(10) = 23
mArrClrIdx(11) = 43
End Sub


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 Long 'Integer
Dim WrkDays As Date
Dim StartDate As Date
Dim Enddate As Date
Dim Day1 As Long ' Integer

Dim rw As Long, col As Long
Dim vVal As Variant
Dim rngLookAt As Range

If mArrClrIdx(0) = 0 Then PopClrArray

'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

Set rngLookAt = Worksheets("Calendar").Range("AE2:AP11")

For Each rngCell In rngData
vVal = rngCell.Value
icolor = -1

For col = 1 To 11 ' ie AE to AP
For rw = 1 To 10 ' ie 2 to 11

If rngLookAt(rw, col).Value = vVal Then
icolor = mArrClrIdx(rw + 1) ' note the +1
Exit For
End If

Next ' rw
If icolor > -1 Then Exit For
Next ' col

If icolor = -1 Then icolor = xlColorIndexNone

' only reformat if necessary
With rngCell.Interior
If .ColorIndex <> icolor Then .ColorIndex = icolor
End If

Next rngCell

End Sub


BTW, if ThisWorkbook.Worksheets("Calendar") is refers to the same worksheet
as the sheet module containing this code, there's no need to qualify the
sheet. In a sheet module Range("A1") will always refer to A1 in its own
sheet.

Regards,
Peter T


"Newman Emanouel" <(E-Mail Removed)> wrote in message
news:4BC21C88-6671-45A2-8E97-(E-Mail Removed)...
> 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
>
>

Peter

Thanks the code looks good but has a End IF Block If Error which I have
tried various combinations and I cannot solve.

The purpose of the code is tohighlight colours on a calendar. I have created
a calendar sheet and then have a list of key dates in the Range("AE5:AP5")
these dates are by month which is why I tried Case but it has limitations. I
think if you can help solve the error above it should do what I need it to so
a lot more efficiently

Thanks

Regards

Newman

"Peter T" wrote:

> I don't really know what you are trying to do, and somehow suspect what you
> are doing isn't quite it. Eg, I doubt you want all that code to run whenever
> some random cell on the sheet changes (you don't refer to Target at all).
> However, hopefully the following should replicate your code more efficiently
> (but I haven't tested so double check)
>
> Option Explicit
> Private mArrClrIdx(0 To 11) As Long
>
> Sub PopClrArray()
> PopClrArray(0) = -1 ' to show the array has been populated
> mArrClrIdx(2) = 43 ' icolor2 etc
> mArrClrIdx(3) = 13
> mArrClrIdx(4) = 39
> mArrClrIdx(5) = 36
> mArrClrIdx(6) = 45
> mArrClrIdx(7) = 33
> mArrClrIdx(8) = 22
> mArrClrIdx(9) = 35
> mArrClrIdx(10) = 23
> mArrClrIdx(11) = 43
> End Sub
>
>
> 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 Long 'Integer
> Dim WrkDays As Date
> Dim StartDate As Date
> Dim Enddate As Date
> Dim Day1 As Long ' Integer
>
> Dim rw As Long, col As Long
> Dim vVal As Variant
> Dim rngLookAt As Range
>
> If mArrClrIdx(0) = 0 Then PopClrArray
>
> '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
>
> Set rngLookAt = Worksheets("Calendar").Range("AE2:AP11")
>
> For Each rngCell In rngData
> vVal = rngCell.Value
> icolor = -1
>
> For col = 1 To 11 ' ie AE to AP
> For rw = 1 To 10 ' ie 2 to 11
>
> If rngLookAt(rw, col).Value = vVal Then
> icolor = mArrClrIdx(rw + 1) ' note the +1
> Exit For
> End If
>
> Next ' rw
> If icolor > -1 Then Exit For
> Next ' col
>
> If icolor = -1 Then icolor = xlColorIndexNone
>
> ' only reformat if necessary
> With rngCell.Interior
> If .ColorIndex <> icolor Then .ColorIndex = icolor
> End If
>
> Next rngCell
>
> End Sub
>
>
> BTW, if ThisWorkbook.Worksheets("Calendar") is refers to the same worksheet
> as the sheet module containing this code, there's no need to qualify the
> sheet. In a sheet module Range("A1") will always refer to A1 in its own
> sheet.
>
> Regards,
> Peter T
>
>
> "Newman Emanouel" <(E-Mail Removed)> wrote in message
> news:4BC21C88-6671-45A2-8E97-(E-Mail Removed)...
> > 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

There were two typos I'm afraid -

> With rngCell.Interior
> If .ColorIndex <> icolor Then .ColorIndex = icolor
> End If

should read

With rngCell.Interior
If .ColorIndex <> icolor Then .ColorIndex = icolor
End With

ie change if to with


> Sub PopClrArray()
> PopClrArray(0) = -1 ' to show the array has been populated
> mArrClrIdx(2) = 43 ' icolor2 etc

should read

Sub PopClrArray()
mArrClrIdx(0) = -1 ' to show the array has been populated
mArrClrIdx(2) = 43 ' icolor2 etc

(not sure how that one crept in!)


If(?) the code goes behind your sheet named "Calendar" I'm almost sure you
only need the code to run unless the target includes one of the cells your
code either changes or looks at. If so start with

If Intersect(Range("B5:X55,AE5:AP5"), Target) Is Nothing Then
Exit Sub
End If

My guess is also you only want to process changinged cells within that
range. Beyond that I can't make sense of the logic.

A minor thing, if the code is in "Calendar" you can change

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

to simply

Set rngData = Range("B5:X55")
Set Wday1data = Range("AE5:AP5")

Regards,
Peter T



"Newman Emanouel" <(E-Mail Removed)> wrote in message
news:05DF5182-423D-45A0-945A-(E-Mail Removed)...
> Peter
>
> Thanks the code looks good but has a End IF Block If Error which I have
> tried various combinations and I cannot solve.
>
> The purpose of the code is tohighlight colours on a calendar. I have
> created
> a calendar sheet and then have a list of key dates in the Range("AE5:AP5")
> these dates are by month which is why I tried Case but it has limitations.
> I
> think if you can help solve the error above it should do what I need it to
> so
> a lot more efficiently
>
> Thanks
>
> Regards
>
> Newman
>
> "Peter T" wrote:
>
>> I don't really know what you are trying to do, and somehow suspect what
>> you
>> are doing isn't quite it. Eg, I doubt you want all that code to run
>> whenever
>> some random cell on the sheet changes (you don't refer to Target at all).
>> However, hopefully the following should replicate your code more
>> efficiently
>> (but I haven't tested so double check)
>>
>> Option Explicit
>> Private mArrClrIdx(0 To 11) As Long
>>
>> Sub PopClrArray()
>> PopClrArray(0) = -1 ' to show the array has been populated
>> mArrClrIdx(2) = 43 ' icolor2 etc
>> mArrClrIdx(3) = 13
>> mArrClrIdx(4) = 39
>> mArrClrIdx(5) = 36
>> mArrClrIdx(6) = 45
>> mArrClrIdx(7) = 33
>> mArrClrIdx(8) = 22
>> mArrClrIdx(9) = 35
>> mArrClrIdx(10) = 23
>> mArrClrIdx(11) = 43
>> End Sub
>>
>>
>> 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 Long 'Integer
>> Dim WrkDays As Date
>> Dim StartDate As Date
>> Dim Enddate As Date
>> Dim Day1 As Long ' Integer
>>
>> Dim rw As Long, col As Long
>> Dim vVal As Variant
>> Dim rngLookAt As Range
>>
>> If mArrClrIdx(0) = 0 Then PopClrArray
>>
>> '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
>>
>> Set rngLookAt = Worksheets("Calendar").Range("AE2:AP11")
>>
>> For Each rngCell In rngData
>> vVal = rngCell.Value
>> icolor = -1
>>
>> For col = 1 To 11 ' ie AE to AP
>> For rw = 1 To 10 ' ie 2 to 11
>>
>> If rngLookAt(rw, col).Value = vVal Then
>> icolor = mArrClrIdx(rw + 1) ' note the +1
>> Exit For
>> End If
>>
>> Next ' rw
>> If icolor > -1 Then Exit For
>> Next ' col
>>
>> If icolor = -1 Then icolor = xlColorIndexNone
>>
>> ' only reformat if necessary
>> With rngCell.Interior
>> If .ColorIndex <> icolor Then .ColorIndex = icolor
>> End If
>>
>> Next rngCell
>>
>> End Sub
>>
>>
>> BTW, if ThisWorkbook.Worksheets("Calendar") is refers to the same
>> worksheet
>> as the sheet module containing this code, there's no need to qualify the
>> sheet. In a sheet module Range("A1") will always refer to A1 in its own
>> sheet.
>>
>> Regards,
>> Peter T
>>
>>
>> "Newman Emanouel" <(E-Mail Removed)> wrote in message
>> news:4BC21C88-6671-45A2-8E97-(E-Mail Removed)...
>> > 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

Peter

Thanks it works well

Regards

Newman


"Peter T" wrote:

> There were two typos I'm afraid -
>
> > With rngCell.Interior
> > If .ColorIndex <> icolor Then .ColorIndex = icolor
> > End If
>
> should read
>
> With rngCell.Interior
> If .ColorIndex <> icolor Then .ColorIndex = icolor
> End With
>
> ie change if to with
>
>
> > Sub PopClrArray()
> > PopClrArray(0) = -1 ' to show the array has been populated
> > mArrClrIdx(2) = 43 ' icolor2 etc
>
> should read
>
> Sub PopClrArray()
> mArrClrIdx(0) = -1 ' to show the array has been populated
> mArrClrIdx(2) = 43 ' icolor2 etc
>
> (not sure how that one crept in!)
>
>
> If(?) the code goes behind your sheet named "Calendar" I'm almost sure you
> only need the code to run unless the target includes one of the cells your
> code either changes or looks at. If so start with
>
> If Intersect(Range("B5:X55,AE5:AP5"), Target) Is Nothing Then
> Exit Sub
> End If
>
> My guess is also you only want to process changinged cells within that
> range. Beyond that I can't make sense of the logic.
>
> A minor thing, if the code is in "Calendar" you can change
>
> With ThisWorkbook.Worksheets("Calendar")
> Set rngData = .Range("B5:X55")
> Set Wday1data = .Range("AE5:AP5")
> End With
>
> to simply
>
> Set rngData = Range("B5:X55")
> Set Wday1data = Range("AE5:AP5")
>
> Regards,
> Peter T
>
>
>
> "Newman Emanouel" <(E-Mail Removed)> wrote in message
> news:05DF5182-423D-45A0-945A-(E-Mail Removed)...
> > Peter
> >
> > Thanks the code looks good but has a End IF Block If Error which I have
> > tried various combinations and I cannot solve.
> >
> > The purpose of the code is tohighlight colours on a calendar. I have
> > created
> > a calendar sheet and then have a list of key dates in the Range("AE5:AP5")
> > these dates are by month which is why I tried Case but it has limitations.
> > I
> > think if you can help solve the error above it should do what I need it to
> > so
> > a lot more efficiently
> >
> > Thanks
> >
> > Regards
> >
> > Newman
> >
> > "Peter T" wrote:
> >
> >> I don't really know what you are trying to do, and somehow suspect what
> >> you
> >> are doing isn't quite it. Eg, I doubt you want all that code to run
> >> whenever
> >> some random cell on the sheet changes (you don't refer to Target at all).
> >> However, hopefully the following should replicate your code more
> >> efficiently
> >> (but I haven't tested so double check)
> >>
> >> Option Explicit
> >> Private mArrClrIdx(0 To 11) As Long
> >>
> >> Sub PopClrArray()
> >> PopClrArray(0) = -1 ' to show the array has been populated
> >> mArrClrIdx(2) = 43 ' icolor2 etc
> >> mArrClrIdx(3) = 13
> >> mArrClrIdx(4) = 39
> >> mArrClrIdx(5) = 36
> >> mArrClrIdx(6) = 45
> >> mArrClrIdx(7) = 33
> >> mArrClrIdx(8) = 22
> >> mArrClrIdx(9) = 35
> >> mArrClrIdx(10) = 23
> >> mArrClrIdx(11) = 43
> >> End Sub
> >>
> >>
> >> 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 Long 'Integer
> >> Dim WrkDays As Date
> >> Dim StartDate As Date
> >> Dim Enddate As Date
> >> Dim Day1 As Long ' Integer
> >>
> >> Dim rw As Long, col As Long
> >> Dim vVal As Variant
> >> Dim rngLookAt As Range
> >>
> >> If mArrClrIdx(0) = 0 Then PopClrArray
> >>
> >> '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
> >>
> >> Set rngLookAt = Worksheets("Calendar").Range("AE2:AP11")
> >>
> >> For Each rngCell In rngData
> >> vVal = rngCell.Value
> >> icolor = -1
> >>
> >> For col = 1 To 11 ' ie AE to AP
> >> For rw = 1 To 10 ' ie 2 to 11
> >>
> >> If rngLookAt(rw, col).Value = vVal Then
> >> icolor = mArrClrIdx(rw + 1) ' note the +1
> >> Exit For
> >> End If
> >>
> >> Next ' rw
> >> If icolor > -1 Then Exit For
> >> Next ' col
> >>
> >> If icolor = -1 Then icolor = xlColorIndexNone
> >>
> >> ' only reformat if necessary
> >> With rngCell.Interior
> >> If .ColorIndex <> icolor Then .ColorIndex = icolor
> >> End If
> >>
> >> Next rngCell
> >>
> >> End Sub
> >>
> >>
> >> BTW, if ThisWorkbook.Worksheets("Calendar") is refers to the same
> >> worksheet
> >> as the sheet module containing this code, there's no need to qualify the
> >> sheet. In a sheet module Range("A1") will always refer to A1 in its own
> >> sheet.
> >>
> >> Regards,
> >> Peter T
> >>
> >>
> >> "Newman Emanouel" <(E-Mail Removed)> wrote in message
> >> news:4BC21C88-6671-45A2-8E97-(E-Mail Removed)...
> >> > 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