Hi Mike,

I think you have two issues. First, if I understand the issue correctly, the
cells in column B have formulae referring to another sheet. Consequently,
those cells will never be 'empty'. I think you really want to check if the
cells, i.e. the result of the formula / cell reference, is zero or not a
blank text string, depending on the result of the formula. Second, if those
are formula in column B, then a change in the result of the formula will not
trigger the Worksheet_Change event.

The following is a modification of your code that I think will do the trick
for you.

Private Sub Worksheet_Calculate()
Dim currCell As Range
For Each currCell In Range("B2:B10")
With currCell
Application.EnableEvents = False
'If I understand you correctly, IsEmpty(.Value) will always be
'false as these cells contain formulae. I think you want to
'check if .Text = "" or .Value = 0, depending on the nature
'of the data in the other worksheet.
If .Value = 0 Then
.Offset(0, 4).ClearContents
Else
With .Offset(0, 4)
.Value = "x"
End With
End If
Application.EnableEvents = True
End With
Next currCell
End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 5).ClearContents
Else
With .Offset(0, 5)
.Value = "x"
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

--
Timothy White
Contract Programmer
Ontario, Canada

<my initialshite<atsympatico<dot<countryCode



"Mike G - D.C." wrote:

I'm new to this; hopefully someone may be able to help. I have a spreadsheet ...
~~~~~
snip
~~~~~