Thread: update cell colour

I've created a conditional formula macro that changes the cell colour based
on the value enter. The cell value is linked to another cell by an array
formula, but the colour of the cell doesn't change when the cell value
changes. It will only update when i select the cell, and not automatically.

Any advice?

Show us the formula, code and some example data.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"bradasley" <bradasley@discussions.microsoft.com> wrote in message
news:1AA713DD-399B-43ED-8EFC-E17F11B2D52F@microsoft.com...
> I've created a conditional formula macro that changes the cell colour
based
> on the value enter. The cell value is linked to another cell by an array
> formula, but the colour of the cell doesn't change when the cell value
> changes. It will only update when i select the cell, and not
automatically.
>
> Any advice?

This is the cell value where the conditional formatting is applied :

=TRANSPOSE('Joe Bloggs'!A5:A255)
(This is another sheet where the data is entered)

This is the macro that defines the colour:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CalculateFull
On Error GoTo ws_exit:
Application.EnableEvents = False
Set WatchRange = Range("A1:IV45")
If Not Intersect(Target, WatchRange) Is Nothing Then
With Target
Select Case .Value
Case "C": Target.Interior.ColorIndex = 4
Case "T": Target.Interior.ColorIndex = 44
Case "L": Target.Interior.ColorIndex = 6
Case "I": Target.Interior.ColorIndex = 53
Case "O": Target.Interior.ColorIndex = 37
Case "H": Target.Interior.ColorIndex = 3
Case "F": Target.Interior.ColorIndex = 0
Case "0": Target.Interior.ColorIndex = 40
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub


"Bob Phillips" wrote:

> Show us the formula, code and some example data.
>
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> news:1AA713DD-399B-43ED-8EFC-E17F11B2D52F@microsoft.com...
> > I've created a conditional formula macro that changes the cell colour
> based
> > on the value enter. The cell value is linked to another cell by an array
> > formula, but the colour of the cell doesn't change when the cell value
> > changes. It will only update when i select the cell, and not
> automatically.
> >
> > Any advice?
>
>
>

Only 2 small changes

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
to
Private Sub Worksheet_Change(ByVal Target As Range)
and
Select Case .Value
to
Select Case ucase(.Value)
to account for lower case c vs C

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"bradasley" <bradasley@discussions.microsoft.com> wrote in message
news:D211BFC4-107B-4452-87BF-D1FC5F11E929@microsoft.com...
> This is the cell value where the conditional formatting is applied :
>
> =TRANSPOSE('Joe Bloggs'!A5:A255)
> (This is another sheet where the data is entered)
>
> This is the macro that defines the colour:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Application.CalculateFull
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> Set WatchRange = Range("A1:IV45")
> If Not Intersect(Target, WatchRange) Is Nothing Then
> With Target
> Select Case .Value
> Case "C": Target.Interior.ColorIndex = 4
> Case "T": Target.Interior.ColorIndex = 44
> Case "L": Target.Interior.ColorIndex = 6
> Case "I": Target.Interior.ColorIndex = 53
> Case "O": Target.Interior.ColorIndex = 37
> Case "H": Target.Interior.ColorIndex = 3
> Case "F": Target.Interior.ColorIndex = 0
> Case "0": Target.Interior.ColorIndex = 40
> End Select
> End With
> End If
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>
> "Bob Phillips" wrote:
>
>> Show us the formula, code and some example data.
>>
>>
>> --
>>
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from the email address if mailing direct)
>>
>> "bradasley" <bradasley@discussions.microsoft.com> wrote in message
>> news:1AA713DD-399B-43ED-8EFC-E17F11B2D52F@microsoft.com...
>> > I've created a conditional formula macro that changes the cell colour
>> based
>> > on the value enter. The cell value is linked to another cell by an
>> > array
>> > formula, but the colour of the cell doesn't change when the cell value
>> > changes. It will only update when i select the cell, and not
>> automatically.
>> >
>> > Any advice?
>>
>>
>>

Don,

Nothing happens, and now the colour will not change even when the cell is
selected.

"Don Guillett" wrote:

> Only 2 small changes
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> to
> Private Sub Worksheet_Change(ByVal Target As Range)
> and
> Select Case .Value
> to
> Select Case ucase(.Value)
> to account for lower case c vs C
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> news:D211BFC4-107B-4452-87BF-D1FC5F11E929@microsoft.com...
> > This is the cell value where the conditional formatting is applied :
> >
> > =TRANSPOSE('Joe Bloggs'!A5:A255)
> > (This is another sheet where the data is entered)
> >
> > This is the macro that defines the colour:
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > Application.CalculateFull
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > Set WatchRange = Range("A1:IV45")
> > If Not Intersect(Target, WatchRange) Is Nothing Then
> > With Target
> > Select Case .Value
> > Case "C": Target.Interior.ColorIndex = 4
> > Case "T": Target.Interior.ColorIndex = 44
> > Case "L": Target.Interior.ColorIndex = 6
> > Case "I": Target.Interior.ColorIndex = 53
> > Case "O": Target.Interior.ColorIndex = 37
> > Case "H": Target.Interior.ColorIndex = 3
> > Case "F": Target.Interior.ColorIndex = 0
> > Case "0": Target.Interior.ColorIndex = 40
> > End Select
> > End With
> > End If
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > "Bob Phillips" wrote:
> >
> >> Show us the formula, code and some example data.
> >>
> >>
> >> --
> >>
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> (remove nothere from the email address if mailing direct)
> >>
> >> "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> >> news:1AA713DD-399B-43ED-8EFC-E17F11B2D52F@microsoft.com...
> >> > I've created a conditional formula macro that changes the cell colour
> >> based
> >> > on the value enter. The cell value is linked to another cell by an
> >> > array
> >> > formula, but the colour of the cell doesn't change when the cell value
> >> > changes. It will only update when i select the cell, and not
> >> automatically.
> >> >
> >> > Any advice?
> >>
> >>
> >>
>
>
>

You would need to use the calculate event:

Private Sub Worksheet_Calculate()
Dim WatchRange As Range, Target As Range
On Error GoTo ws_exit:
Set WatchRange = Range("A1:IV45")
For Each Target In WatchRange
With Target
Select Case UCase(.Value)
Case "C": Target.Interior.ColorIndex = 4
Case "T": Target.Interior.ColorIndex = 44
Case "L": Target.Interior.ColorIndex = 6
Case "I": Target.Interior.ColorIndex = 53
Case "O": Target.Interior.ColorIndex = 37
Case "H": Target.Interior.ColorIndex = 3
Case "F": Target.Interior.ColorIndex = 0
Case "0": Target.Interior.ColorIndex = 40
End Select
End With
End If
ws_exit:
End Sub

--
Regards,
Tom Ogilvy



"bradasley" <bradasley@discussions.microsoft.com> wrote in message
news:20335893-883E-4CC2-BB1E-434A96F5D7DA@microsoft.com...
> Don,
>
> Nothing happens, and now the colour will not change even when the cell is
> selected.
>
> "Don Guillett" wrote:
>
> > Only 2 small changes
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > to
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > and
> > Select Case .Value
> > to
> > Select Case ucase(.Value)
> > to account for lower case c vs C
> >
> > --
> > Don Guillett
> > SalesAid Software
> > dguillett1@austin.rr.com
> > "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> > news:D211BFC4-107B-4452-87BF-D1FC5F11E929@microsoft.com...
> > > This is the cell value where the conditional formatting is applied :
> > >
> > > =TRANSPOSE('Joe Bloggs'!A5:A255)
> > > (This is another sheet where the data is entered)
> > >
> > > This is the macro that defines the colour:
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > Application.CalculateFull
> > > On Error GoTo ws_exit:
> > > Application.EnableEvents = False
> > > Set WatchRange = Range("A1:IV45")
> > > If Not Intersect(Target, WatchRange) Is Nothing Then
> > > With Target
> > > Select Case .Value
> > > Case "C": Target.Interior.ColorIndex = 4
> > > Case "T": Target.Interior.ColorIndex = 44
> > > Case "L": Target.Interior.ColorIndex = 6
> > > Case "I": Target.Interior.ColorIndex = 53
> > > Case "O": Target.Interior.ColorIndex = 37
> > > Case "H": Target.Interior.ColorIndex = 3
> > > Case "F": Target.Interior.ColorIndex = 0
> > > Case "0": Target.Interior.ColorIndex = 40
> > > End Select
> > > End With
> > > End If
> > > ws_exit:
> > > Application.EnableEvents = True
> > > End Sub
> > >
> > >
> > > "Bob Phillips" wrote:
> > >
> > >> Show us the formula, code and some example data.
> > >>
> > >>
> > >> --
> > >>
> > >> HTH
> > >>
> > >> Bob Phillips
> > >>
> > >> (remove nothere from the email address if mailing direct)
> > >>
> > >> "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> > >> news:1AA713DD-399B-43ED-8EFC-E17F11B2D52F@microsoft.com...
> > >> > I've created a conditional formula macro that changes the cell
colour
> > >> based
> > >> > on the value enter. The cell value is linked to another cell by an
> > >> > array
> > >> > formula, but the colour of the cell doesn't change when the cell
value
> > >> > changes. It will only update when i select the cell, and not
> > >> automatically.
> > >> >
> > >> > Any advice?
> > >>
> > >>
> > >>
> >
> >
> >

This still doesn't work and now comes up with an error with the end if
statement.


"Tom Ogilvy" wrote:

> You would need to use the calculate event:
>
> Private Sub Worksheet_Calculate()
> Dim WatchRange As Range, Target As Range
> On Error GoTo ws_exit:
> Set WatchRange = Range("A1:IV45")
> For Each Target In WatchRange
> With Target
> Select Case UCase(.Value)
> Case "C": Target.Interior.ColorIndex = 4
> Case "T": Target.Interior.ColorIndex = 44
> Case "L": Target.Interior.ColorIndex = 6
> Case "I": Target.Interior.ColorIndex = 53
> Case "O": Target.Interior.ColorIndex = 37
> Case "H": Target.Interior.ColorIndex = 3
> Case "F": Target.Interior.ColorIndex = 0
> Case "0": Target.Interior.ColorIndex = 40
> End Select
> End With
> End If
> ws_exit:
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> news:20335893-883E-4CC2-BB1E-434A96F5D7DA@microsoft.com...
> > Don,
> >
> > Nothing happens, and now the colour will not change even when the cell is
> > selected.
> >
> > "Don Guillett" wrote:
> >
> > > Only 2 small changes
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > to
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > and
> > > Select Case .Value
> > > to
> > > Select Case ucase(.Value)
> > > to account for lower case c vs C
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > dguillett1@austin.rr.com
> > > "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> > > news:D211BFC4-107B-4452-87BF-D1FC5F11E929@microsoft.com...
> > > > This is the cell value where the conditional formatting is applied :
> > > >
> > > > =TRANSPOSE('Joe Bloggs'!A5:A255)
> > > > (This is another sheet where the data is entered)
> > > >
> > > > This is the macro that defines the colour:
> > > >
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > Application.CalculateFull
> > > > On Error GoTo ws_exit:
> > > > Application.EnableEvents = False
> > > > Set WatchRange = Range("A1:IV45")
> > > > If Not Intersect(Target, WatchRange) Is Nothing Then
> > > > With Target
> > > > Select Case .Value
> > > > Case "C": Target.Interior.ColorIndex = 4
> > > > Case "T": Target.Interior.ColorIndex = 44
> > > > Case "L": Target.Interior.ColorIndex = 6
> > > > Case "I": Target.Interior.ColorIndex = 53
> > > > Case "O": Target.Interior.ColorIndex = 37
> > > > Case "H": Target.Interior.ColorIndex = 3
> > > > Case "F": Target.Interior.ColorIndex = 0
> > > > Case "0": Target.Interior.ColorIndex = 40
> > > > End Select
> > > > End With
> > > > End If
> > > > ws_exit:
> > > > Application.EnableEvents = True
> > > > End Sub
> > > >
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > >> Show us the formula, code and some example data.
> > > >>
> > > >>
> > > >> --
> > > >>
> > > >> HTH
> > > >>
> > > >> Bob Phillips
> > > >>
> > > >> (remove nothere from the email address if mailing direct)
> > > >>
> > > >> "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> > > >> news:1AA713DD-399B-43ED-8EFC-E17F11B2D52F@microsoft.com...
> > > >> > I've created a conditional formula macro that changes the cell
> colour
> > > >> based
> > > >> > on the value enter. The cell value is linked to another cell by an
> > > >> > array
> > > >> > formula, but the colour of the cell doesn't change when the cell
> value
> > > >> > changes. It will only update when i select the cell, and not
> > > >> automatically.
> > > >> >
> > > >> > Any advice?
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
>

Sorry, that was a typo (easily debugged).

Private Sub Worksheet_Calculate()
Dim WatchRange As Range, Target As Range
On Error GoTo ws_exit:
Set WatchRange = Range("A1:IV45")
For Each Target In WatchRange
With Target
Select Case UCase(.Value)
Case "C": Target.Interior.ColorIndex = 4
Case "T": Target.Interior.ColorIndex = 44
Case "L": Target.Interior.ColorIndex = 6
Case "I": Target.Interior.ColorIndex = 53
Case "O": Target.Interior.ColorIndex = 37
Case "H": Target.Interior.ColorIndex = 3
Case "F": Target.Interior.ColorIndex = 0
Case "0": Target.Interior.ColorIndex = 40
End Select
End With
Next
ws_exit:
End Sub

worked fine for me.

--
Regards,
Tom Ogilvy


"bradasley" <bradasley@discussions.microsoft.com> wrote in message
news:EDC0D8C9-2717-4D19-94B8-C4A5F3D632B4@microsoft.com...
> This still doesn't work and now comes up with an error with the end if
> statement.
>
>
> "Tom Ogilvy" wrote:
>
> > You would need to use the calculate event:
> >
> > Private Sub Worksheet_Calculate()
> > Dim WatchRange As Range, Target As Range
> > On Error GoTo ws_exit:
> > Set WatchRange = Range("A1:IV45")
> > For Each Target In WatchRange
> > With Target
> > Select Case UCase(.Value)
> > Case "C": Target.Interior.ColorIndex = 4
> > Case "T": Target.Interior.ColorIndex = 44
> > Case "L": Target.Interior.ColorIndex = 6
> > Case "I": Target.Interior.ColorIndex = 53
> > Case "O": Target.Interior.ColorIndex = 37
> > Case "H": Target.Interior.ColorIndex = 3
> > Case "F": Target.Interior.ColorIndex = 0
> > Case "0": Target.Interior.ColorIndex = 40
> > End Select
> > End With
> > End If
> > ws_exit:
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> > news:20335893-883E-4CC2-BB1E-434A96F5D7DA@microsoft.com...
> > > Don,
> > >
> > > Nothing happens, and now the colour will not change even when the cell
is
> > > selected.
> > >
> > > "Don Guillett" wrote:
> > >
> > > > Only 2 small changes
> > > >
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > to
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > and
> > > > Select Case .Value
> > > > to
> > > > Select Case ucase(.Value)
> > > > to account for lower case c vs C
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > dguillett1@austin.rr.com
> > > > "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> > > > news:D211BFC4-107B-4452-87BF-D1FC5F11E929@microsoft.com...
> > > > > This is the cell value where the conditional formatting is applied
:
> > > > >
> > > > > =TRANSPOSE('Joe Bloggs'!A5:A255)
> > > > > (This is another sheet where the data is entered)
> > > > >
> > > > > This is the macro that defines the colour:
> > > > >
> > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > Application.CalculateFull
> > > > > On Error GoTo ws_exit:
> > > > > Application.EnableEvents = False
> > > > > Set WatchRange = Range("A1:IV45")
> > > > > If Not Intersect(Target, WatchRange) Is Nothing Then
> > > > > With Target
> > > > > Select Case .Value
> > > > > Case "C": Target.Interior.ColorIndex = 4
> > > > > Case "T": Target.Interior.ColorIndex = 44
> > > > > Case "L": Target.Interior.ColorIndex = 6
> > > > > Case "I": Target.Interior.ColorIndex = 53
> > > > > Case "O": Target.Interior.ColorIndex = 37
> > > > > Case "H": Target.Interior.ColorIndex = 3
> > > > > Case "F": Target.Interior.ColorIndex = 0
> > > > > Case "0": Target.Interior.ColorIndex = 40
> > > > > End Select
> > > > > End With
> > > > > End If
> > > > > ws_exit:
> > > > > Application.EnableEvents = True
> > > > > End Sub
> > > > >
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > >> Show us the formula, code and some example data.
> > > > >>
> > > > >>
> > > > >> --
> > > > >>
> > > > >> HTH
> > > > >>
> > > > >> Bob Phillips
> > > > >>
> > > > >> (remove nothere from the email address if mailing direct)
> > > > >>
> > > > >> "bradasley" <bradasley@discussions.microsoft.com> wrote in
message
> > > > >> news:1AA713DD-399B-43ED-8EFC-E17F11B2D52F@microsoft.com...
> > > > >> > I've created a conditional formula macro that changes the cell
> > colour
> > > > >> based
> > > > >> > on the value enter. The cell value is linked to another cell
by an
> > > > >> > array
> > > > >> > formula, but the colour of the cell doesn't change when the
cell
> > value
> > > > >> > changes. It will only update when i select the cell, and not
> > > > >> automatically.
> > > > >> >
> > > > >> > Any advice?
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
> >

Cheers Tom,

Problem sorted and very much appreciated....Thanks...

"Tom Ogilvy" wrote:

> Sorry, that was a typo (easily debugged).
>
> Private Sub Worksheet_Calculate()
> Dim WatchRange As Range, Target As Range
> On Error GoTo ws_exit:
> Set WatchRange = Range("A1:IV45")
> For Each Target In WatchRange
> With Target
> Select Case UCase(.Value)
> Case "C": Target.Interior.ColorIndex = 4
> Case "T": Target.Interior.ColorIndex = 44
> Case "L": Target.Interior.ColorIndex = 6
> Case "I": Target.Interior.ColorIndex = 53
> Case "O": Target.Interior.ColorIndex = 37
> Case "H": Target.Interior.ColorIndex = 3
> Case "F": Target.Interior.ColorIndex = 0
> Case "0": Target.Interior.ColorIndex = 40
> End Select
> End With
> Next
> ws_exit:
> End Sub
>
> worked fine for me.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> news:EDC0D8C9-2717-4D19-94B8-C4A5F3D632B4@microsoft.com...
> > This still doesn't work and now comes up with an error with the end if
> > statement.
> >
> >
> > "Tom Ogilvy" wrote:
> >
> > > You would need to use the calculate event:
> > >
> > > Private Sub Worksheet_Calculate()
> > > Dim WatchRange As Range, Target As Range
> > > On Error GoTo ws_exit:
> > > Set WatchRange = Range("A1:IV45")
> > > For Each Target In WatchRange
> > > With Target
> > > Select Case UCase(.Value)
> > > Case "C": Target.Interior.ColorIndex = 4
> > > Case "T": Target.Interior.ColorIndex = 44
> > > Case "L": Target.Interior.ColorIndex = 6
> > > Case "I": Target.Interior.ColorIndex = 53
> > > Case "O": Target.Interior.ColorIndex = 37
> > > Case "H": Target.Interior.ColorIndex = 3
> > > Case "F": Target.Interior.ColorIndex = 0
> > > Case "0": Target.Interior.ColorIndex = 40
> > > End Select
> > > End With
> > > End If
> > > ws_exit:
> > > End Sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> > > news:20335893-883E-4CC2-BB1E-434A96F5D7DA@microsoft.com...
> > > > Don,
> > > >
> > > > Nothing happens, and now the colour will not change even when the cell
> is
> > > > selected.
> > > >
> > > > "Don Guillett" wrote:
> > > >
> > > > > Only 2 small changes
> > > > >
> > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > to
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > and
> > > > > Select Case .Value
> > > > > to
> > > > > Select Case ucase(.Value)
> > > > > to account for lower case c vs C
> > > > >
> > > > > --
> > > > > Don Guillett
> > > > > SalesAid Software
> > > > > dguillett1@austin.rr.com
> > > > > "bradasley" <bradasley@discussions.microsoft.com> wrote in message
> > > > > news:D211BFC4-107B-4452-87BF-D1FC5F11E929@microsoft.com...
> > > > > > This is the cell value where the conditional formatting is applied
> :
> > > > > >
> > > > > > =TRANSPOSE('Joe Bloggs'!A5:A255)
> > > > > > (This is another sheet where the data is entered)
> > > > > >
> > > > > > This is the macro that defines the colour:
> > > > > >
> > > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > > Application.CalculateFull
> > > > > > On Error GoTo ws_exit:
> > > > > > Application.EnableEvents = False
> > > > > > Set WatchRange = Range("A1:IV45")
> > > > > > If Not Intersect(Target, WatchRange) Is Nothing Then
> > > > > > With Target
> > > > > > Select Case .Value
> > > > > > Case "C": Target.Interior.ColorIndex = 4
> > > > > > Case "T": Target.Interior.ColorIndex = 44
> > > > > > Case "L": Target.Interior.ColorIndex = 6
> > > > > > Case "I": Target.Interior.ColorIndex = 53
> > > > > > Case "O": Target.Interior.ColorIndex = 37
> > > > > > Case "H": Target.Interior.ColorIndex = 3
> > > > > > Case "F": Target.Interior.ColorIndex = 0
> > > > > > Case "0": Target.Interior.ColorIndex = 40
> > > > > > End Select
> > > > > > End With
> > > > > > End If
> > > > > > ws_exit:
> > > > > > Application.EnableEvents = True
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > >> Show us the formula, code and some example data.
> > > > > >>
> > > > > >>
> > > > > >> --
> > > > > >>
> > > > > >> HTH
> > > > > >>
> > > > > >> Bob Phillips
> > > > > >>
> > > > > >> (remove nothere from the email address if mailing direct)
> > > > > >>
> > > > > >> "bradasley" <bradasley@discussions.microsoft.com> wrote in
> message
> > > > > >> news:1AA713DD-399B-43ED-8EFC-E17F11B2D52F@microsoft.com...
> > > > > >> > I've created a conditional formula macro that changes the cell
> > > colour
> > > > > >> based
> > > > > >> > on the value enter. The cell value is linked to another cell
> by an
> > > > > >> > array
> > > > > >> > formula, but the colour of the cell doesn't change when the
> cell
> > > value
> > > > > >> > changes. It will only update when i select the cell, and not
> > > > > >> automatically.
> > > > > >> >
> > > > > >> > Any advice?
> > > > > >>
> > > > > >>
> > > > > >>
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>