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.