Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > update cell colour
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default 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?


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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?



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default 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?





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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?






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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?








Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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?









Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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?











Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default 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?












Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can colour of cell shading be fixed to one colour Tabrez Excel Discussion (Misc queries) 2 September 23rd 08 04:55 PM
Data Validation lists update orginal cell with list update [email protected] Excel Worksheet Functions 3 July 11th 08 07:56 AM
how to update certain text in a cell according to thecell colour Ashish Patil Excel Worksheet Functions 1 April 30th 08 07:06 PM
change a cell background colour to my own RGB colour requirements Stephen Doughty Excel Discussion (Misc queries) 4 June 16th 06 01:08 PM
Update Macro: Leave Colour As Before Once Cell is Not Active JB2010 Excel Discussion (Misc queries) 2 February 2nd 06 06:08 PM


All times are GMT +1. The time now is 09:32 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement