| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case statement comparing two ranges
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case statement comparing two ranges
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case statement comparing two ranges
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case statement comparing two ranges
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" wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case statement comparing two ranges
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" wrote in message ... 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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using LIKE in case statement | Excel Programming | |||
| Case Statement | Excel Discussion (Misc queries) | |||
| Comparing Cell Contents using a Case statement | Excel Programming | |||
| Comparing Sheets while ignoring Case. | Excel Discussion (Misc queries) | |||
| Question about comparing mixed case string values | Excel Programming | |||
Linear Mode
