|
Conditional Formatting- Refresh
I have added a VB script to a sheet to change color of the cell based on input
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F1:F510")) Is Nothing Then Select Case Target Case "Red" icolor = 3 Case "Green" icolor = 4 Case "Blue" icolor = 5 Case "White" icolor = 2 Case "Gray" icolor = 15 Case "" icolor = 0 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor Target.Font.ColorIndex = icolor End If End Sub --------------------------------------------------- This works well if the data is entered manually. some cells that have a calculated input using a formula does not get updated. It works if I use "Conditional Formatting" but the limitation there is 3 colours. Any help will be appreciated. |
Conditional Formatting- Refresh
Use the calculate event and cycle through each cell in the range.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ram B" wrote in message ... I have added a VB script to a sheet to change color of the cell based on input Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F1:F510")) Is Nothing Then Select Case Target Case "Red" icolor = 3 Case "Green" icolor = 4 Case "Blue" icolor = 5 Case "White" icolor = 2 Case "Gray" icolor = 15 Case "" icolor = 0 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor Target.Font.ColorIndex = icolor End If End Sub --------------------------------------------------- This works well if the data is entered manually. some cells that have a calculated input using a formula does not get updated. It works if I use "Conditional Formatting" but the limitation there is 3 colours. Any help will be appreciated. |
Conditional Formatting- Refresh
The reason it does not change the cell with the formula is because it is not
the "target" cell, but is only affected by the "target" cell. I don't know what version you are running. I am running 2002. In 2002 Conditional Formatting has all the Excel standard colors, not just 3. Format Conditional Formatting Click the Format button Click the Pattern tab. All Excel standard colors are available here. Conditional Formatting is the easiest way to go. You could calculate each cell in the range after a change occurs. Alan "Ram B" wrote: I have added a VB script to a sheet to change color of the cell based on input Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F1:F510")) Is Nothing Then Select Case Target Case "Red" icolor = 3 Case "Green" icolor = 4 Case "Blue" icolor = 5 Case "White" icolor = 2 Case "Gray" icolor = 15 Case "" icolor = 0 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor Target.Font.ColorIndex = icolor End If End Sub --------------------------------------------------- This works well if the data is entered manually. some cells that have a calculated input using a formula does not get updated. It works if I use "Conditional Formatting" but the limitation there is 3 colours. Any help will be appreciated. |
Conditional Formatting- Refresh
I am engineer with little knowledge of VB. Would it be possible for you to help me with the code? "Bob Phillips" wrote: Use the calculate event and cycle through each cell in the range. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ram B" wrote in message ... I have added a VB script to a sheet to change color of the cell based on input Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F1:F510")) Is Nothing Then Select Case Target Case "Red" icolor = 3 Case "Green" icolor = 4 Case "Blue" icolor = 5 Case "White" icolor = 2 Case "Gray" icolor = 15 Case "" icolor = 0 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor Target.Font.ColorIndex = icolor End If End Sub --------------------------------------------------- This works well if the data is entered manually. some cells that have a calculated input using a formula does not get updated. It works if I use "Conditional Formatting" but the limitation there is 3 colours. Any help will be appreciated. |
Conditional Formatting- Refresh
Here you are
Private Sub Worksheet_Calculate() Dim icolor1 As Long Dim icolor2 As Long Dim cell As Range For Each cell In Range("F1:F510") icolor1 = xlColorIndexNone icolor2 = xlColorIndexAutomatic Select Case cell.Value Case "Red": icolor1 = 3: icolor2 = 3 Case "Green": icolor1 = 4: icolor2 = 4 Case "Blue": icolor1 = 5: icolor2 = 5 Case "White": icolor1 = 2: icolor2 = 2 Case "Gray": icolor1 = 15: icolor2 = 15 Case Else: 'Whatever End Select cell.Interior.ColorIndex = icolor1 cell.Font.ColorIndex = icolor2 Next cell End Sub -- __________________________________ HTH Bob "Ram B" wrote in message ... I am engineer with little knowledge of VB. Would it be possible for you to help me with the code? "Bob Phillips" wrote: Use the calculate event and cycle through each cell in the range. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ram B" wrote in message ... I have added a VB script to a sheet to change color of the cell based on input Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F1:F510")) Is Nothing Then Select Case Target Case "Red" icolor = 3 Case "Green" icolor = 4 Case "Blue" icolor = 5 Case "White" icolor = 2 Case "Gray" icolor = 15 Case "" icolor = 0 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor Target.Font.ColorIndex = icolor End If End Sub --------------------------------------------------- This works well if the data is entered manually. some cells that have a calculated input using a formula does not get updated. It works if I use "Conditional Formatting" but the limitation there is 3 colours. Any help will be appreciated. |
Conditional Formatting- Refresh
Not 3 colours, 3 conditions.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan" wrote in message ... The reason it does not change the cell with the formula is because it is not the "target" cell, but is only affected by the "target" cell. I don't know what version you are running. I am running 2002. In 2002 Conditional Formatting has all the Excel standard colors, not just 3. Format Conditional Formatting Click the Format button Click the Pattern tab. All Excel standard colors are available here. Conditional Formatting is the easiest way to go. You could calculate each cell in the range after a change occurs. Alan "Ram B" wrote: I have added a VB script to a sheet to change color of the cell based on input Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F1:F510")) Is Nothing Then Select Case Target Case "Red" icolor = 3 Case "Green" icolor = 4 Case "Blue" icolor = 5 Case "White" icolor = 2 Case "Gray" icolor = 15 Case "" icolor = 0 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor Target.Font.ColorIndex = icolor End If End Sub --------------------------------------------------- This works well if the data is entered manually. some cells that have a calculated input using a formula does not get updated. It works if I use "Conditional Formatting" but the limitation there is 3 colours. Any help will be appreciated. |
Conditional Formatting- Refresh
Thanks Works like a charm
"Bob Phillips" wrote: Here you are Private Sub Worksheet_Calculate() Dim icolor1 As Long Dim icolor2 As Long Dim cell As Range For Each cell In Range("F1:F510") icolor1 = xlColorIndexNone icolor2 = xlColorIndexAutomatic Select Case cell.Value Case "Red": icolor1 = 3: icolor2 = 3 Case "Green": icolor1 = 4: icolor2 = 4 Case "Blue": icolor1 = 5: icolor2 = 5 Case "White": icolor1 = 2: icolor2 = 2 Case "Gray": icolor1 = 15: icolor2 = 15 Case Else: 'Whatever End Select cell.Interior.ColorIndex = icolor1 cell.Font.ColorIndex = icolor2 Next cell End Sub -- __________________________________ HTH Bob "Ram B" wrote in message ... I am engineer with little knowledge of VB. Would it be possible for you to help me with the code? "Bob Phillips" wrote: Use the calculate event and cycle through each cell in the range. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ram B" wrote in message ... I have added a VB script to a sheet to change color of the cell based on input Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F1:F510")) Is Nothing Then Select Case Target Case "Red" icolor = 3 Case "Green" icolor = 4 Case "Blue" icolor = 5 Case "White" icolor = 2 Case "Gray" icolor = 15 Case "" icolor = 0 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor Target.Font.ColorIndex = icolor End If End Sub --------------------------------------------------- This works well if the data is entered manually. some cells that have a calculated input using a formula does not get updated. It works if I use "Conditional Formatting" but the limitation there is 3 colours. Any help will be appreciated. |
Conditional Formatting- Refresh
Thanks Bob, I didn't catch that. I'll read closer next time.
Alan "Bob Phillips" wrote: Not 3 colours, 3 conditions. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Alan" wrote in message ... The reason it does not change the cell with the formula is because it is not the "target" cell, but is only affected by the "target" cell. I don't know what version you are running. I am running 2002. In 2002 Conditional Formatting has all the Excel standard colors, not just 3. Format Conditional Formatting Click the Format button Click the Pattern tab. All Excel standard colors are available here. Conditional Formatting is the easiest way to go. You could calculate each cell in the range after a change occurs. Alan "Ram B" wrote: I have added a VB script to a sheet to change color of the cell based on input Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("F1:F510")) Is Nothing Then Select Case Target Case "Red" icolor = 3 Case "Green" icolor = 4 Case "Blue" icolor = 5 Case "White" icolor = 2 Case "Gray" icolor = 15 Case "" icolor = 0 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor Target.Font.ColorIndex = icolor End If End Sub --------------------------------------------------- This works well if the data is entered manually. some cells that have a calculated input using a formula does not get updated. It works if I use "Conditional Formatting" but the limitation there is 3 colours. Any help will be appreciated. |
| All times are GMT +1. The time now is 09:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com