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?

update cell colour
 

Show us the formula, code and some example data.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"bradasley" wrote in message
...
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?

update cell colour
 

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" wrote in message
...
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?

update cell colour
 

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

"bradasley" wrote in message
...
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" wrote in message
...
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?

update cell colour
 

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

"bradasley" wrote in message
...
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" wrote in message
...
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?

update cell colour
 

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" wrote in message
...
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

"bradasley" wrote in message
...
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" wrote in message
...
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?

update cell colour
 

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" wrote in message
...
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

"bradasley" wrote in message
...
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" wrote in message
...
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?

update cell colour
 

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" wrote in message
...
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" wrote in message
...
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

"bradasley" wrote in message
...
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" wrote in
message
...
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?

update cell colour
 

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" wrote in message
...
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" wrote in message
...
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

"bradasley" wrote in message
...
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" wrote in
message
...
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?