Worksheet_Change vs Worksheet_Calculate

[Beans]

Hi below is my code that is working fine if I type values into columns D or E.
As you can see I have a number of different values to evaluate (more than
conditional formating would allow argghh) and based on the letter, change the
background color. This code resides in my Summary.xls

Now what I really want is to link the values in Summary Column D and E to a
Detail.xls So that for example Summary!D1 = Details!Q23.

If I have both workbooks open, and I make changes to Detail!Q23 the value
of Summary!D1 changes but the background color does not change.

As I read through various posts I understand that if a cell is a formula
then the Worksheet_Change does not recognize it as a change and that
Worksheet_calculate could be used. I'm having trouble understanding the
posts as to how to update my routine to trigger the evaluation using
calculate instead of Change.

Any help would be greatly appreciate.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("D:E")) Is Nothing Then
If Target.Value = "G" Then
On Error Resume Next
Application.EnableEvents = False
Target.Interior.ColorIndex = 4
Application.EnableEvents = True
On Error GoTo 0
Else
If Target.Value = "R" Then
On Error Resume Next
Application.EnableEvents = False
Target.Interior.ColorIndex = 3
Application.EnableEvents = True
On Error GoTo 0
Else
If Target.Value = "Y" Then
On Error Resume Next
Application.EnableEvents = False
Target.Interior.ColorIndex = 6
Application.EnableEvents = True
On Error GoTo 0
Else
If Target.Value = "B" Then
On Error Resume Next
Application.EnableEvents = False
Target.Interior.ColorIndex = 5
Application.EnableEvents = True
On Error GoTo 0
Else
If Target.Value = "Gr" Then
On Error Resume Next
Application.EnableEvents = False
Target.Interior.ColorIndex = 15
Application.EnableEvents = True
On Error GoTo 0
End If
End If
End If
End If
End If
End If
End Sub

 

[Dave Peterson]

I would just loop through the D column inspecting the values.

And I don't quite get how column E is used.

But if your formulas are in column D, then maybe something like:

Option Explicit
Private Sub Worksheet_Calculate()
Dim myRng As Range
Dim myCell As Range
Dim myColor As Long

With Me
Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
End With

For Each myCell In myRng.Cells
myColor = -1
Select Case UCase(myCell.Value)
Case Is = "G": myColor = 4
Case Is = "R": myColor = 3
Case Is = "Y": myColor = 6
Case Is = "B": myColor = 5
Case Is = UCase("Gr"): myColor = 15
End Select

If myColor < 0 Then
'do nothing, not one of those choices
Else
myCell.Interior.ColorIndex = myColor
End If
Next myCell

End Sub

Changing colors won't cause any events to fire--so I don't have to worry about
..enableevents.

And I'm not sure why you're doing the "on error" stuff. (Is the sheet
protected???)

 

[Beans]

Hi Dave, thanks very much for the code. It worked beautifully. Column E
also has a formula referencing a column in Detail and needed to be evaluated
for content to change the background colour. All I did was update your code
include E in the range ( see below) and when I change content of Column Q and
R in Detail the background in Summary D and E changes perfectly.
As for your other question on the on error messages, I must confess I found
this code in another posting so I really can't answer.
Thanks again


Set myRng = .Range("D1", .Cells(.Rows.Count, "E").End(xlUp))