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.

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" <(E-Mail Removed)> wrote in message
news:6717DF5B-3C5E-4383-AE8C-(E-Mail Removed)...
>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.
>

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.
>

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" <(E-Mail Removed)> wrote in message
> news:6717DF5B-3C5E-4383-AE8C-(E-Mail Removed)...
> >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" <(E-Mail Removed)> wrote in message
news:02ABD4B8-B833-45FF-8105-(E-Mail Removed)...
>
> 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" <(E-Mail Removed)> wrote in message
>> news:6717DF5B-3C5E-4383-AE8C-(E-Mail Removed)...
>> >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.
>> >
>>
>>
>>

Not 3 colours, 3 conditions.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Alan" <(E-Mail Removed)> wrote in message
news:1C623B0C-3C63-4E04-8E88-(E-Mail Removed)...
> 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.
>>

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" <(E-Mail Removed)> wrote in message
> news:02ABD4B8-B833-45FF-8105-(E-Mail Removed)...
> >
> > 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" <(E-Mail Removed)> wrote in message
> >> news:6717DF5B-3C5E-4383-AE8C-(E-Mail Removed)...
> >> >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 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" <(E-Mail Removed)> wrote in message
> news:1C623B0C-3C63-4E04-8E88-(E-Mail Removed)...
> > 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.
> >>
>
>
>