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

How about:

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"))

'no colors to start in all the range
myRng.Interior.ColorIndex = xlNone

For Each myCell In myRng.Cells

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
myCell.Interior.ColorIndex = 3
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, 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

--

Dave Peterson

On Jul 24, 9:36*am, Dave Peterson wrote:
How about:

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"))

* * * * 'no colors to start in all the range
* * * * myRng.Interior.ColorIndex = xlNone

* * * * For Each myCell In myRng.Cells

* * * * * * 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
* * * * * * * * * * * * myCell.Interior.ColorIndex = 3
* * * * * * * * * * 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, *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

--

Dave Peterson

Hi Dave, works perfect. Thank you for your assistance, wish I could
buy you a couple of beers. Thanks, Ron Smith

Glad it's working.

If you're going to hang around in these Microsoft excel newsgroups, you may have
noticed that most people are top posters (different than in most general
newsgroups).

You may want to start doing the same.

Ron wrote:

<<snipped

Hi Dave, works perfect. Thank you for your assistance, wish I could
buy you a couple of beers. Thanks, Ron Smith

--

Dave Peterson

On Jul 27, 12:02*pm, Dave Peterson wrote:
Glad it's working.

Ifyou're going to hang around in these Microsoft excel newsgroups, you may have
noticed that most people are top posters (different than in most general
newsgroups).

You may want to start doing the same.



Ron wrote:

<<snipped

Hi Dave, works perfect. *Thank you for your assistance, wish I could
buy you a couple of beers. *Thanks, Ron Smith

--

Dave Peterson

Hi Dave, I don't understand your reply. Am I doing something wrong?
Thanks, Ron

Nothing wrong--just something uncommon in these newsgroup.

Most people put their response at the top of the new message (Top posting). You
put your response at the bottom (bottom posting).



Ron wrote:

On Jul 27, 12:02 pm, Dave Peterson wrote:
Glad it's working.

Ifyou're going to hang around in these Microsoft excel newsgroups, you may have
noticed that most people are top posters (different than in most general
newsgroups).

You may want to start doing the same.



Ron wrote:

<<snipped

Hi Dave, works perfect. Thank you for your assistance, wish I could
buy you a couple of beers. Thanks, Ron Smith

--

Dave Peterson

Hi Dave, I don't understand your reply. Am I doing something wrong?
Thanks, Ron

--

Dave Peterson

Is this better? Sorry, I've never paid attention. Thanks for the
heads up. Have a good one, Ron

On Jul 27, 2:10*pm, Dave Peterson wrote:
Nothing wrong--just something uncommon in these newsgroup.

Most people put their response at the top of the new message (Top posting). *You
put your response at the bottom (bottom posting).





Ron wrote:

On Jul 27, 12:02 pm, Dave Peterson wrote:
Glad it's working.

Ifyou're going to hang around in these Microsoft excel newsgroups, you may have
noticed that most people are top posters (different than in most general
newsgroups).

You may want to start doing the same.

Ron wrote:

<<snipped

Hi Dave, works perfect. *Thank you for your assistance, wish I could
buy you a couple of beers. *Thanks, Ron Smith

--

Dave Peterson

Hi Dave, I don't understand your reply. *Am I doing something wrong?
Thanks, Ron

--

Dave Peterson- Hide quoted text -

- Show quoted text -

I like it better!



Ron wrote:

Is this better? Sorry, I've never paid attention. Thanks for the
heads up. Have a good one, Ron

On Jul 27, 2:10 pm, Dave Peterson wrote:
Nothing wrong--just something uncommon in these newsgroup.

Most people put their response at the top of the new message (Top posting). You
put your response at the bottom (bottom posting).





Ron wrote:

On Jul 27, 12:02 pm, Dave Peterson wrote:
Glad it's working.

Ifyou're going to hang around in these Microsoft excel newsgroups, you may have
noticed that most people are top posters (different than in most general
newsgroups).

You may want to start doing the same.

Ron wrote:

<<snipped

Hi Dave, works perfect. Thank you for your assistance, wish I could
buy you a couple of beers. Thanks, Ron Smith

--

Dave Peterson

Hi Dave, I don't understand your reply. Am I doing something wrong?
Thanks, Ron

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson