Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format macro not working - almost there
Hi
I'm trying to do a macro to conditionally format some cells depending on there value this is the code i have written, but it only works if the whole range is a certain number and not on the individual cells within the range. 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 'Set the range for the formatting Set r = Intersect(Range("Ranges1"), 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("Ranges1") Select Case cell Case 1 Selection.Interior.ColorIndex = 2 Case 2, 3 Selection.Interior.ColorIndex = 4 Case 4 To 6 Selection.Interior.ColorIndex = 6 Case Is 8 Selection.Interior.ColorIndex = 8 Case Else Selection.Interior.ColorIndex = 10 End Select Next End Sub All help is appreciated -- _______________________ Naz, London |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format macro not working - almost there
Hi Naz,
Try this modification of your code: '============= 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("Ranges1"), 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("Ranges1") With cell Select Case cell Case 1 .Interior.ColorIndex = 2 Case 2, 3 .Interior.ColorIndex = 4 Case 4 To 6 .Interior.ColorIndex = 6 Case Is 8 .Interior.ColorIndex = 8 Case Else .Interior.ColorIndex = 10 End Select End With Next End Sub '<<============= --- Regards, Norman "Naz" wrote in message ... Hi I'm trying to do a macro to conditionally format some cells depending on there value this is the code i have written, but it only works if the whole range is a certain number and not on the individual cells within the range. 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 'Set the range for the formatting Set r = Intersect(Range("Ranges1"), 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("Ranges1") Select Case cell Case 1 Selection.Interior.ColorIndex = 2 Case 2, 3 Selection.Interior.ColorIndex = 4 Case 4 To 6 Selection.Interior.ColorIndex = 6 Case Is 8 Selection.Interior.ColorIndex = 8 Case Else Selection.Interior.ColorIndex = 10 End Select Next End Sub All help is appreciated -- _______________________ Naz, London |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format macro not working - almost there
....don't re-invent the wheel. Just conditionally format the cells based on
the given criteria and leave the macro out. "Naz" wrote: Hi I'm trying to do a macro to conditionally format some cells depending on there value this is the code i have written, but it only works if the whole range is a certain number and not on the individual cells within the range. 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 'Set the range for the formatting Set r = Intersect(Range("Ranges1"), 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("Ranges1") Select Case cell Case 1 Selection.Interior.ColorIndex = 2 Case 2, 3 Selection.Interior.ColorIndex = 4 Case 4 To 6 Selection.Interior.ColorIndex = 6 Case Is 8 Selection.Interior.ColorIndex = 8 Case Else Selection.Interior.ColorIndex = 10 End Select Next End Sub All help is appreciated -- _______________________ Naz, London |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
conditional format macro not working - almost there
Not trying to re-invent the wheel, i'm going to need to use this code with
upto 20 conditions which the normal conditional format won't be able to do, as its limited to only 4 (incl default) conditions. -- _______________________ Naz, London "JR" wrote: ...don't re-invent the wheel. Just conditionally format the cells based on the given criteria and leave the macro out. "Naz" wrote: Hi I'm trying to do a macro to conditionally format some cells depending on there value this is the code i have written, but it only works if the whole range is a certain number and not on the individual cells within the range. 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 'Set the range for the formatting Set r = Intersect(Range("Ranges1"), 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("Ranges1") Select Case cell Case 1 Selection.Interior.ColorIndex = 2 Case 2, 3 Selection.Interior.ColorIndex = 4 Case 4 To 6 Selection.Interior.ColorIndex = 6 Case Is 8 Selection.Interior.ColorIndex = 8 Case Else Selection.Interior.ColorIndex = 10 End Select Next End Sub All help is appreciated -- _______________________ Naz, London |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format Not Working | Excel Discussion (Misc queries) | |||
Conditional Format Not Working | Excel Worksheet Functions | |||
Conditional Formatting Not Working In Macro | Excel Discussion (Misc queries) | |||
Conditional Format as a MACRO | Excel Worksheet Functions | |||
Conditional Format Not Working | Excel Discussion (Misc queries) |