Here is the code.....
I know it works. It works on ever cell on the page except for one wit
a lot of text in it.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngA As Range, rngB As Range, cell As Range
If IsNumeric(Target.Value) = True Then
If Target.Value < 0 Then
Target.Value = Abs(Target.Value)
End If
If Int(Target.Value) - (Target.Value) = 0 Then
ActiveSheet.Unprotect Password:="finance"
Target.Interior.ColorIndex = xlNone
ActiveSheet.Protect Password:="finance", DrawingObjects:=True
Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True
AllowSorting:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
End If
If Not IsNumeric(Target.Value) = True Then
'-I used the On Error Resume Next / On Error Goto 0 statements to forc
the code to ingnore my "too much text" issue......if that is what th
problem is-
On Error Resume Next
With Worksheets("Budget Summary")
Set rngA = .Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
End With
For Each cell In Target
Set rngB = rngA.Find( _
What:=Target.Value, _
After:=rngA(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
If Not rngB Is Nothing Then
ActiveSheet.Unprotect Password:="finance"
Target.Interior.ColorIndex = xlNone
ActiveSheet.Protect Password:="finance", DrawingObjects:=True
Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True
AllowSorting:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
On Error GoTo 0
If IsEmpty(cell) Then
ActiveSheet.Unprotect Password:="finance"
cell.Interior.ColorIndex = xlNone
ActiveSheet.Protect Password:="finance", DrawingObjects:=True
Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True
AllowSorting:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
Next
End If
End Su
--
Cel
-----------------------------------------------------------------------
Celt's Profile:
http://www.excelforum.com/member.php...fo&userid=1941
View this thread:
http://www.excelforum.com/showthread.php?threadid=55013