I have a range of cells; non-contiguous; namely

("D109, C111:K111, C114:K114")

What I would like to achieve is that when this range is complete with data
to have a macro run - lets call it "SetHR".

The range of cells is in a sheet called ("Current Round").

Now my thinking was to trigger the macro in the Worksheet_Change event - but
only trigger it when all cells in the range contain data (which is a mixture
of text and numeric), and the focus has now moved outwith the above range.
Furthermore not to run "SetHR" until any further change occurs to the above
range.

Hope this makes sense.

Any thoughts?

Sandy

Sandy,

Right click your sheet tab, view code and paste this in. When all cells in
your range are populeted it call your macro and won't call it again until a
cell changes.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim FilledCells As Long, TotCells As Long
Set MyRange = Range("D109, C111:K111, C114:K114")
If Intersect(Target, MyRange) Is Nothing Then Exit Sub

FilledCells = WorksheetFunction.CountA(MyRange)
TotCells = MyRange.Cells.Count
If FilledCells = TotCells Then
Call SetHR
End If
End Sub

Mike

"Sandy" wrote:

I have a range of cells; non-contiguous; namely

("D109, C111:K111, C114:K114")

What I would like to achieve is that when this range is complete with data
to have a macro run - lets call it "SetHR".

The range of cells is in a sheet called ("Current Round").

Now my thinking was to trigger the macro in the Worksheet_Change event - but
only trigger it when all cells in the range contain data (which is a mixture
of text and numeric), and the focus has now moved outwith the above range.
Furthermore not to run "SetHR" until any further change occurs to the above
range.

Hope this makes sense.

Any thoughts?

Sandy

Mike
Thank you very much, works perfectly.
Sandy

"Mike H" wrote in message
...
Sandy,

Right click your sheet tab, view code and paste this in. When all cells in
your range are populeted it call your macro and won't call it again until
a
cell changes.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim FilledCells As Long, TotCells As Long
Set MyRange = Range("D109, C111:K111, C114:K114")
If Intersect(Target, MyRange) Is Nothing Then Exit Sub

FilledCells = WorksheetFunction.CountA(MyRange)
TotCells = MyRange.Cells.Count
If FilledCells = TotCells Then
Call SetHR
End If
End Sub

Mike

"Sandy" wrote:

I have a range of cells; non-contiguous; namely

("D109, C111:K111, C114:K114")

What I would like to achieve is that when this range is complete with
data
to have a macro run - lets call it "SetHR".

The range of cells is in a sheet called ("Current Round").

Now my thinking was to trigger the macro in the Worksheet_Change event -
but
only trigger it when all cells in the range contain data (which is a
mixture
of text and numeric), and the focus has now moved outwith the above
range.
Furthermore not to run "SetHR" until any further change occurs to the
above
range.

Hope this makes sense.

Any thoughts?

Sandy