Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally formatting
Can someone tell me please how I can do the following, I am trying to
conditionally format a range of cells which sum from others. I have used the following: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim icolor As Integer If Intersect(Target, Range("P3:W3000")) Is Nothing Then Select Case Target Case 1 To 50 icolor = 35 Case 51 To 100 icolor = 36 Case Is = 101 icolor = 3 Case Else 'icolor = 0 End Select Target.Interior.ColorIndex = icolor End If End Sub but this changes the cell that I am working on and not the range in the target which is summing. Please can someone provide me with a solution? Many thanks -- Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditionally formatting
Private Sub Worksheet_Calculate()
Dim icolor As Integer for each cell in Range("P3:W3000") Select Case cell Case 1 To 50 icolor = 35 Case 51 To 100 icolor = 36 Case Is = 101 icolor = 3 Case Else icolor = xlNone End Select cell.Interior.ColorIndex = icolor End If End Sub Change P3:W3000 to reflect the range where you want the conditional coloring applied. Also note that you can get up to 3 conditions (plus the default) in the built in conditional formatting option under the format menu. -- Regards, Tom Ogilvy "Mark" wrote: Can someone tell me please how I can do the following, I am trying to conditionally format a range of cells which sum from others. I have used the following: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim icolor As Integer If Intersect(Target, Range("P3:W3000")) Is Nothing Then Select Case Target Case 1 To 50 icolor = 35 Case 51 To 100 icolor = 36 Case Is = 101 icolor = 3 Case Else 'icolor = 0 End Select Target.Interior.ColorIndex = icolor End If End Sub but this changes the cell that I am working on and not the range in the target which is summing. Please can someone provide me with a solution? Many thanks -- Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditionally formatting | Excel Worksheet Functions | |||
Conditionally formatting currency | Excel Worksheet Functions | |||
Conditionally Formatting | Excel Worksheet Functions | |||
Conditionally formatting rows | Excel Worksheet Functions | |||
Conditionally formatting other cells? | Excel Worksheet Functions |