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.

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.

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.

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.