| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case Select
Hello,
I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
Private Sub Worksheet_Change(ByVal Target As Range)
' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. Select Case Target.Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jimbola" wrote in message ... Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
On Sun, 11 Dec 2005 07:59:02 -0800, "Jimbola"
wrote: Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Not sure what you mean by "doesn't work". But the following code work fine using similar logic. If you could define, more clearly, what "doesn't work" means ... ===================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) Select Case Range("F1") Case Is < 20 Range("A1").Interior.Color = vbRed Case Is < 50 Range("A1").Interior.Color = vbGreen Case Else Range("A1").Interior.Color = vbBlue End Select End Sub ====================== Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J --ron |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
Sorry Bob I don't get this, it doesn't work. Thanks anyway
Let me re-explain the problem i am having, my original code works fine, but for the first test rather than put the criteria in the code i would like it in A30 as it can change. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. Select Case Target.Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jimbola" wrote in message ... Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
Sorry i'm not being clear. What i want if for the criteria for select case to
reside in a cell. So for example in the code you gave instead of habing Case is <20 i want something like case range("A30") and for the criteria e.g. <20 to reisde in A30 so it can be changed on the sheet rather the code. In a similar way that i have the colour picked up from the work sheet in my original code by using offset. "Ron Rosenfeld" wrote: On Sun, 11 Dec 2005 07:59:02 -0800, "Jimbola" wrote: Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Not sure what you mean by "doesn't work". But the following code work fine using similar logic. If you could define, more clearly, what "doesn't work" means ... ===================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) Select Case Range("F1") Case Is < 20 Range("A1").Interior.Color = vbRed Case Is < 50 Range("A1").Interior.Color = vbGreen Case Else Range("A1").Interior.Color = vbBlue End Select End Sub ====================== Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J --ron |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
not sure if this is what you want, and i don't have your range, but it will
set cell A3 to the color depending on the value in A30 Option Explicit Dim cell As Range Sub test() Set cell = Range("A3") With cell Select Case Range("a30").Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With End Sub -- Gary "Jimbola" wrote in message ... Sorry Bob I don't get this, it doesn't work. Thanks anyway Let me re-explain the problem i am having, my original code works fine, but for the first test rather than put the criteria in the code i would like it in A30 as it can change. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. Select Case Target.Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jimbola" wrote in message ... Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
sorry nope, its the actual criteria 50 that i want in cell A30, so it can be
changed on the sheet rather than the code. thanks "Gary Keramidas" wrote: not sure if this is what you want, and i don't have your range, but it will set cell A3 to the color depending on the value in A30 Option Explicit Dim cell As Range Sub test() Set cell = Range("A3") With cell Select Case Range("a30").Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With End Sub -- Gary "Jimbola" wrote in message ... Sorry Bob I don't get this, it doesn't work. Thanks anyway Let me re-explain the problem i am having, my original code works fine, but for the first test rather than put the criteria in the code i would like it in A30 as it can change. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. Select Case Target.Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jimbola" wrote in message ... Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
something like this?
Case Is 50 .Interior.ColorIndex = Range("A30").Value then when a30 is change the colorindex changes -- Gary "Jimbola" wrote in message ... sorry nope, its the actual criteria 50 that i want in cell A30, so it can be changed on the sheet rather than the code. thanks "Gary Keramidas" wrote: not sure if this is what you want, and i don't have your range, but it will set cell A3 to the color depending on the value in A30 Option Explicit Dim cell As Range Sub test() Set cell = Range("A3") With cell Select Case Range("a30").Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With End Sub -- Gary "Jimbola" wrote in message ... Sorry Bob I don't get this, it doesn't work. Thanks anyway Let me re-explain the problem i am having, my original code works fine, but for the first test rather than put the criteria in the code i would like it in A30 as it can change. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. Select Case Target.Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jimbola" wrote in message ... Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
sorry no, more like
case range("A30") where A30 can contain the criteria (i.e 50) ..Interior.ColorIndex = Range("Condition").Offset(1, 2) < this works OK not the problem have just a number in A30 is can deal with but have the operand = < and so on i can't figure "Gary Keramidas" wrote: something like this? Case Is 50 .Interior.ColorIndex = Range("A30").Value then when a30 is change the colorindex changes -- Gary "Jimbola" wrote in message ... sorry nope, its the actual criteria 50 that i want in cell A30, so it can be changed on the sheet rather than the code. thanks "Gary Keramidas" wrote: not sure if this is what you want, and i don't have your range, but it will set cell A3 to the color depending on the value in A30 Option Explicit Dim cell As Range Sub test() Set cell = Range("A3") With cell Select Case Range("a30").Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With End Sub -- Gary "Jimbola" wrote in message ... Sorry Bob I don't get this, it doesn't work. Thanks anyway Let me re-explain the problem i am having, my original code works fine, but for the first test rather than put the criteria in the code i would like it in A30 as it can change. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. Select Case Target.Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jimbola" wrote in message ... Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
On Sun, 11 Dec 2005 09:09:01 -0800, "Jimbola"
wrote: Sorry i'm not being clear. What i want if for the criteria for select case to reside in a cell. So for example in the code you gave instead of habing Case is <20 i want something like case range("A30") and for the criteria e.g. <20 to reisde in A30 so it can be changed on the sheet rather the code. In a similar way that i have the colour picked up from the work sheet in my original code by using offset. OK, then something like this: ========================================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim expressionlist() As Long Dim c As Range Dim i As Long Dim ExpressionListRange As Range Set ExpressionListRange = [g1:g3] ReDim expressionlist(ExpressionListRange.Count) For Each c In ExpressionListRange expressionlist(i) = c.Value i = i + 1 Next c Select Case Range("F1") Case Is < expressionlist(0) Range("A1").Interior.Color = vbRed Case Is < expressionlist(1) Range("A1").Interior.Color = vbGreen Case Is < expressionlist(2) Range("A1").Interior.Color = vbYellow Case Else Range("A1").Interior.Color = vbBlue End Select End Sub ========================== or ================================== Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim c As Range Select Case Range("F1") Case Is < Range("g1").Value Range("A1").Interior.Color = vbRed Case Is < Range("g2").Value Range("A1").Interior.Color = vbGreen Case Is < Range("g3").Value Range("A1").Interior.Color = vbYellow Case Else Range("A1").Interior.Color = vbBlue End Select End Sub =================================== --ron |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
I couldn't obtain the right results with a case select, so i did it
with a For ... Next loop: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim cell As Range If Not IsEmpty(Target) Then With Target For Each cell In [Formats] If Evaluate(.Value & cell) = True Then cell.Offset(0, 1).Copy .PasteSpecial xlPasteFormats Application.CutCopyMode = False End If Next cell End With End If End Sub I have a named range, "Formats", which is A1:A3, holding the text "<50","=50","50". Using the Evaluate function, I effectively test If ("49<50"=TRUE) Then ... The loop checks all the criteria in A1:A3, and if it finds a match, it copies the format from the cell next to the criterium to the target cell. In the example of the target cell holding '53', it would copy the format of cell B3 (since the text "50" is in A3) to the active cell. Let me know if you get this to work/if it helped. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Case Select
another guess
c = Range("a30").Value Case Is c .Interior.ColorIndex = Range("Condition").Offset(1, 2) -- Gary "Jimbola" wrote in message ... sorry no, more like case range("A30") where A30 can contain the criteria (i.e 50) .Interior.ColorIndex = Range("Condition").Offset(1, 2) < this works OK not the problem have just a number in A30 is can deal with but have the operand = < and so on i can't figure "Gary Keramidas" wrote: something like this? Case Is 50 .Interior.ColorIndex = Range("A30").Value then when a30 is change the colorindex changes -- Gary "Jimbola" wrote in message ... sorry nope, its the actual criteria 50 that i want in cell A30, so it can be changed on the sheet rather than the code. thanks "Gary Keramidas" wrote: not sure if this is what you want, and i don't have your range, but it will set cell A3 to the color depending on the value in A30 Option Explicit Dim cell As Range Sub test() Set cell = Range("A3") With cell Select Case Range("a30").Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With End Sub -- Gary "Jimbola" wrote in message ... Sorry Bob I don't get this, it doesn't work. Thanks anyway Let me re-explain the problem i am having, my original code works fine, but for the first test rather than put the criteria in the code i would like it in A30 as it can change. "Bob Phillips" wrote: Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. Select Case Target.Value Case Is 50 .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Jimbola" wrote in message ... Hello, I've written this code to get round the conditional format limit. The first case tests if the cell 50, but instead of changing the code i want the cell A30 to hold the criteria as it can change. But using Case range("A30") doesn't work. Can someone please point me in the write direction Private Sub Worksheet_Change(ByVal Target As Range) ' This macro is activated everytime a change is made to ' this worksheet (TestArea) Dim r As Range Dim cell As Range 'Set the range for the formatting Set r = Intersect(Range("shtRange"), Target) ' If the change in the worksheet is not in the ' tested range, exit the macro. If r Is Nothing Then Exit Sub 'Change to formatting of the cell that changed. For Each cell In Range("BoxRange") With cell Select Case cell Case Is 50 <<<<<<<<<<<<<<<<< .Interior.ColorIndex = Range("Condition").Offset(1, 2) Case 2 .Interior.ColorIndex = 2 Case 3 .Interior.ColorIndex = 3 Case 4 .Interior.ColorIndex = 4 Case 5 .Interior.ColorIndex = 5 Case 6 .Interior.ColorIndex = 6 Case 7 .Interior.ColorIndex = 7 Case 8 .Interior.ColorIndex = 8 Case 9 .Interior.ColorIndex = 9 Case 10 .Interior.ColorIndex = 10 Case Else .Interior.ColorIndex = 0 End Select End With Next End Sub Many thanks J |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Select Case ignored | Excel Worksheet Functions | |||
| Case without Select Case error problem | Excel Discussion (Misc queries) | |||
| SELECT CASE - Which Row am I on? | Excel Programming | |||
| Case Select | Excel Programming | |||
| For Each with Select Case | Excel Programming | |||
Linear Mode
