B
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
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