Dave - one mistake I had in the prior code that I failed to mention is
that
what I'm truly trying to do is pass a cell to a function whenever the
cell changes - (at some point I'd like to exclude being cleared or set
to blank)
- find the location (really just the column) of that cell - and then
find the values of surrounding cells based on the location of the cell
being passed.

So I changed the arguments to read

= Range(celladdress).Offset(n,m)

where n,m indicate the direction from the reference cell.




Dave Peterson wrote:
Without looking too much, you could be more explicit:

Private Function EntryIsValid(cell) As Variant
becomes
Private Function EntryIsValid(cell as range) As Variant


And I've found that it's better for me to be explicit when I refer to
an
object--instead of relying on its default property.

If CInt(cell) < cell Then
becomes:
If CInt(cell.value) < cell.value Then
instead of just plain cell.
(and other places, too)

Dept = Range("A1").Offset(0, -5).value
instead of
Dept = Range("A1").Offset(0, -5)



I find I make fewer mistakes while writing the code initially and it
makes it
easier when reviewing it later.




" wrote:

On other quick question -

I'm trying to pass the "Cell" to the Function, not just its value,
so
that I can figure out its address and column - but when I pass

EntryIsValid(cell) it appears its passing the value I put into the
cell

When I pass cell.address - then I can't figure out how to use that
to
use
Range().Offset....

Dave Peterson wrote:
Try moving your worksheet_change event into the sheet module that
should have
that behavior.

There is a workbook version of the worksheet_change event--it's
called by:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target
As
Range)



" wrote:

Can someone explain to me why, when I type in the area
"A1:F65536"
this
doesn't seem to execute?

Code in ThisWorkbook

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim VRange As Range, cell As Range
Dim Msg As String
Dim ValidateCode As Variant
Set VRange = Range("A1:F65536")
For Each cell In Target
If Union(cell, VRange).Address = VRange.Address Then
ValidateCode = EntryIsValid(cell)
If ValidateCode = False Then
MsgBox "Please make correct entry"
Else
MsgBox "Dept: " & ValidateCode(1) & vbCrLf & _
"Loc: " & ValidateCode(2) & vbCrLf & _
"Fn: " & ValidateCode(3) & vbCrLf & _
"Acct: " & ValidateCode(4) & vbCrLf & _
"Fleet: " & ValidateCode(5) & vbCrLf & _
"Bldg: " & ValidateCode(6)
Application.EnableEvents = False
cell.Activate
Application.EnableEvents = True
End If
End If
Next cell
End Sub

Code in module1

Private Function EntryIsValid(cell) As Variant
Option Base 1
Dim Dept, Loc, Fn, Acct, Fleet, Bldg As Variant
If Not WorksheeetFunction.IsNumber(cell) Then
EntryIsValid = False
Exit Function
End If
If CInt(cell) < cell Then
EntryIsValid = False
Exit Function
End If
CELLCOLUMN = Left(CStr(cell.Address), 1)
Select Case CELLCOLUMN
Case "A"
Dept = Range("A1").Offset(0, 0)
Loc = Range("A1").Offset(0, 1)
Fn = Range("A1").Offset(0, 2)
Acct = Range("A1").Offset(0, 3)
Fleet = Range("A1").Offset(0, 4)
Bldg = Range("A1").Offset(0, 5)
Case "B"
Dept = Range("A1").Offset(0, -1)
Loc = Range("A1").Offset(0, 0)
Fn = Range("A1").Offset(0, 1)
Acct = Range("A1").Offset(0, 2)
Fleet = Range("A1").Offset(0, 3)
Bldg = Range("A1").Offset(0, 4)
Case "C"
Dept = Range("A1").Offset(0, -2)
Loc = Range("A1").Offset(0, -1)
Fn = Range("A1").Offset(0, 0)
Acct = Range("A1").Offset(0, 1)
Fleet = Range("A1").Offset(0, 2)
Bldg = Range("A1").Offset(0, 3)
Case "D"
Dept = Range("A1").Offset(0, -3)
Loc = Range("A1").Offset(0, -2)
Fn = Range("A1").Offset(0, -1)
Acct = Range("A1").Offset(0, 0)
Fleet = Range("A1").Offset(0, 1)
Bldg = Range("A1").Offset(0, 2)
Case "E"
Dept = Range("A1").Offset(0, -4)
Loc = Range("A1").Offset(0, -3)
Fn = Range("A1").Offset(0, -2)
Acct = Range("A1").Offset(0, -1)
Fleet = Range("A1").Offset(0, 0)
Bldg = Range("A1").Offset(0, 1)
Case "F"
Dept = Range("A1").Offset(0, -5)
Loc = Range("A1").Offset(0, -4)
Fn = Range("A1").Offset(0, -3)
Acct = Range("A1").Offset(0, -2)
Fleet = Range("A1").Offset(0, -1)
Bldg = Range("A1").Offset(0, 0)
Case Else
End Select
EntryIsValid(1) = Dept
EntryIsValid(2) = Loc
EntryIsValid(3) = Fn
EntryIsValid(4) = Acct
EntryIsValid(5) = Fleet
EntryIsValid(6) = Bldg
End Function

--

Dave Peterson


--

Dave Peterson