On Jul 23, 3:58*pm, Dave Peterson wrote:
I missed that last colorindex in your original code:

Option Explicit
Sub ValDataN()
* * Dim myCell As Range
* * Dim myRng As Range
* * Dim LastRow As Long
* * Dim wks As Worksheet
* * Dim ErrStr As String
* * Dim HowManyErrors As Long

* * ErrStr = "Needs Dept ID"
* * Set wks = ActiveSheet

* * With wks
* * * * .Unprotect
* * * * LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
* * * * Set myRng = .Range("N12", .Cells(LastRow, "N"))

* * * * For Each myCell In myRng.Cells
* * * * * * myCell.Interior.ColorIndex = xlNone
* * * * * * If Len(myCell.Value) 0 _
* * * * * * *And Len(myCell.Value) < 6 Then
* * * * * * * * myCell.Interior.ColorIndex = 3
* * * * * * End If

* * * * * * Select Case myCell.Offset(0, -3).Value
* * * * * * * * Case 100000 To 330000, 939001 To 939002
* * * * * * * * * * myCell.ClearContents
* * * * * * * * Case 400000 To 799999, Is = 940000
* * * * * * * * * * If myCell.Value = "" Then
* * * * * * * * * * * * myCell.Value = ErrStr
* * * * * * * * * * End If
* * * * * * End Select
* * * * Next myCell
* * * * .Protect
* * End With

* * HowManyErrors = Application.CountIf(myRng, ErrStr)
* * If HowManyErrors 0 Then
* * * * MsgBox "Please fix: " & HowManyErrors & " records!" _
* * * * * * * * * * & vbLf & "Search for: " & ErrStr
* * End If
End Sub

Ron wrote:

<<snipped





Hi Dave, thank you. *Wow! what a great interpretation This code is
hot.... only one problem. *Works perfect first time through, however
When I run it a second time the code applys interior color to a blank
cell that is in case 100000 To 330000, 939001 To 939002. * *Deletes
Dept ID like it should, however if I run it again it will highlight
the blank cells created by case 1. *Any suggestions? *Thank you, Ron

The second case must have a Dept ID and that's working perfect

Select Case myCell.Offset(0, -3).Value
* * * * * * * * Case 100000 To 330000, 939001 To 939002
* * * * * * * * * * myCell.ClearContents
* * * * * * * * Case 400000 To 799999, Is = 940000
* * * * * * * * * * If myCell.Value = "" Then
* * * * * * * * * * * * myCell.Value = ErrStr
* * * * * * * * * * End If

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hi Dave, when I run it for the first time on a worksheet it finds
case 2 scnarios and adds Needs Dept ID, but does not color the cell
red. When I run it the second time it finds case 2 scnario and colors
them red. Any way to get it to color the cell red the first time
through. Other than that it works great and thank you for your
assistance and follow ups. Ron