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 |
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 |
All times are GMT +1. The time now is 06:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com