update cell colour

[Guest]

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?

 

[Bob Phillips]

Show us the formula, code and some example data.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

Don,

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

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

Cheers Tom,

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