| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formating on Worksheet Change
I've found this macro and I wonder what might need to be changed to do what I
want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formating on Worksheet Change
If you are always going to be changing the colorindex of cells 26
columns to the left of the cell that is changed (e.g., change cell AC2 leads to changing colorindex of cell C2), something like this may work for you. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Select Case Target.Value Case 1 icolor = 39 Case 2 icolor = 13 Case 3 icolor = 5 Case 4 icolor = 6 Case 5 icolor = 45 Case Else icolor = 3 End Select Target.Offset(0, -26).Interior.ColorIndex = icolor End Sub Mark Barb Reinhardt wrote: I've found this macro and I wonder what might need to be changed to do what I want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formating on Worksheet Change
I'd use something like:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B1:T14").Offset(0, 26)) Is Nothing Then Exit Sub End If iColor = -9999 Select Case Target.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then Target.Offset(0, -26).Interior.ColorIndex = iColor End If End Sub Barb Reinhardt wrote: I've found this macro and I wonder what might need to be changed to do what I want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formating on Worksheet Change
Will this work if the worksheet changes based upon a calculated value in
AB2:AT14? "Dave Peterson" wrote: I'd use something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B1:T14").Offset(0, 26)) Is Nothing Then Exit Sub End If iColor = -9999 Select Case Target.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then Target.Offset(0, -26).Interior.ColorIndex = iColor End If End Sub Barb Reinhardt wrote: I've found this macro and I wonder what might need to be changed to do what I want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formating on Worksheet Change
Nope.
Worksheet_change is an event that fires when someone changes something manually. You could use the worksheet_Calculate event, though: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long Dim myCell As Range Dim myRng As Range Set myRng = Me.Range("b1:t14").Offset(0, 26) For Each myCell In myRng.Cells iColor = -9999 Select Case myCell.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then myCell.Offset(0, -26).Interior.ColorIndex = iColor End If Next myCell End Sub Barb Reinhardt wrote: Will this work if the worksheet changes based upon a calculated value in AB2:AT14? "Dave Peterson" wrote: I'd use something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B1:T14").Offset(0, 26)) Is Nothing Then Exit Sub End If iColor = -9999 Select Case Target.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then Target.Offset(0, -26).Interior.ColorIndex = iColor End If End Sub Barb Reinhardt wrote: I've found this macro and I wonder what might need to be changed to do what I want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formating on Worksheet Change
I changed it to this:
Option Explicit Private Sub Worksheet_Calculate(ByVal Target As Range) Dim iColor As Long Dim myCell As Range Dim myRng As Range Set myRng = Me.Range("b1:t14").Offset(0, 26) For Each myCell In myRng.Cells iColor = -9999 Select Case myCell.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then myCell.Offset(0, -26).Interior.ColorIndex = iColor End If Next myCell End Sub and got the following error: Procedure declaration does not match description of event or procedure having the same name. What do I need to change? "Dave Peterson" wrote: Nope. Worksheet_change is an event that fires when someone changes something manually. You could use the worksheet_Calculate event, though: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long Dim myCell As Range Dim myRng As Range Set myRng = Me.Range("b1:t14").Offset(0, 26) For Each myCell In myRng.Cells iColor = -9999 Select Case myCell.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then myCell.Offset(0, -26).Interior.ColorIndex = iColor End If Next myCell End Sub Barb Reinhardt wrote: Will this work if the worksheet changes based upon a calculated value in AB2:AT14? "Dave Peterson" wrote: I'd use something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B1:T14").Offset(0, 26)) Is Nothing Then Exit Sub End If iColor = -9999 Select Case Target.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then Target.Offset(0, -26).Interior.ColorIndex = iColor End If End Sub Barb Reinhardt wrote: I've found this macro and I wonder what might need to be changed to do what I want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formating on Worksheet Change
Oopsie.
Try changing it to this: Private Sub Worksheet_Calculate() (I made the changes, compiled and forgot to change the procedure's name!) But if you're using these kinds of events, you can always use those dropdowns at the top of the code window to choose the object and event (worksheet and change in this case). Barb Reinhardt wrote: I changed it to this: Option Explicit Private Sub Worksheet_Calculate(ByVal Target As Range) Dim iColor As Long Dim myCell As Range Dim myRng As Range Set myRng = Me.Range("b1:t14").Offset(0, 26) For Each myCell In myRng.Cells iColor = -9999 Select Case myCell.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then myCell.Offset(0, -26).Interior.ColorIndex = iColor End If Next myCell End Sub and got the following error: Procedure declaration does not match description of event or procedure having the same name. What do I need to change? "Dave Peterson" wrote: Nope. Worksheet_change is an event that fires when someone changes something manually. You could use the worksheet_Calculate event, though: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long Dim myCell As Range Dim myRng As Range Set myRng = Me.Range("b1:t14").Offset(0, 26) For Each myCell In myRng.Cells iColor = -9999 Select Case myCell.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then myCell.Offset(0, -26).Interior.ColorIndex = iColor End If Next myCell End Sub Barb Reinhardt wrote: Will this work if the worksheet changes based upon a calculated value in AB2:AT14? "Dave Peterson" wrote: I'd use something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B1:T14").Offset(0, 26)) Is Nothing Then Exit Sub End If iColor = -9999 Select Case Target.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then Target.Offset(0, -26).Interior.ColorIndex = iColor End If End Sub Barb Reinhardt wrote: I've found this macro and I wonder what might need to be changed to do what I want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formating on Worksheet Change
What would I need to add to change the font color. I'd like font color WHITE
and BLACK. Can I do something like this: Case Is = 6: iColor = 3: fcolor = 1 "Dave Peterson" wrote: Nope. Worksheet_change is an event that fires when someone changes something manually. You could use the worksheet_Calculate event, though: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long Dim myCell As Range Dim myRng As Range Set myRng = Me.Range("b1:t14").Offset(0, 26) For Each myCell In myRng.Cells iColor = -9999 Select Case myCell.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then myCell.Offset(0, -26).Interior.ColorIndex = iColor End If Next myCell End Sub Barb Reinhardt wrote: Will this work if the worksheet changes based upon a calculated value in AB2:AT14? "Dave Peterson" wrote: I'd use something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B1:T14").Offset(0, 26)) Is Nothing Then Exit Sub End If iColor = -9999 Select Case Target.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then Target.Offset(0, -26).Interior.ColorIndex = iColor End If End Sub Barb Reinhardt wrote: I've found this macro and I wonder what might need to be changed to do what I want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formating on Worksheet Change
Test it out on a small subset of the lines and see what happens!
Barb Reinhardt wrote: What would I need to add to change the font color. I'd like font color WHITE and BLACK. Can I do something like this: Case Is = 6: iColor = 3: fcolor = 1 "Dave Peterson" wrote: Nope. Worksheet_change is an event that fires when someone changes something manually. You could use the worksheet_Calculate event, though: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long Dim myCell As Range Dim myRng As Range Set myRng = Me.Range("b1:t14").Offset(0, 26) For Each myCell In myRng.Cells iColor = -9999 Select Case myCell.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then myCell.Offset(0, -26).Interior.ColorIndex = iColor End If Next myCell End Sub Barb Reinhardt wrote: Will this work if the worksheet changes based upon a calculated value in AB2:AT14? "Dave Peterson" wrote: I'd use something like: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim iColor As Long If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("B1:T14").Offset(0, 26)) Is Nothing Then Exit Sub End If iColor = -9999 Select Case Target.Value Case Is = 6: iColor = 3 Case Is = 5: iColor = 45 Case Is = 4: iColor = 6 Case Is = 3: iColor = 5 Case Is = 2: iColor = 13 Case Is = 1: iColor = 39 End Select If iColor -9999 Then Target.Offset(0, -26).Interior.ColorIndex = iColor End If End Sub Barb Reinhardt wrote: I've found this macro and I wonder what might need to be changed to do what I want to do: http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 icolor = 53 Case 21 To 25 icolor = 15 Case 26 To 30 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub The range I want to format is B1:T14 and the formulas I want to use a To color cell B2: Formula currently in place is If AB2 =6, Highlight color = 3 If AB2 = 5, Highlight color = 45 IF AB2 =4, Highlight color = 6 If AB2 =3, Highlight color = 5 IF AB2=2, Highlight color = 13 If AB2 = 1, Highlight color = 39 For C2, I'm checking against AC2. Can someone assist? Thanks in advance, Barb Reinhardt -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formating with Icon set - How to change the color | Excel Discussion (Misc queries) | |||
| Change color without conditional formating | Excel Discussion (Misc queries) | |||
| Conditional formating-change color of cells over than one with for | Excel Discussion (Misc queries) | |||
| Conditional Formating - Change Color | Excel Programming | |||
| How to change an entire row using conditional formating | Excel Discussion (Misc queries) | |||
Hybrid Mode
