Data entered in one record is shifting to others

[shekpatrick]

I have a large spreadsheet that is attempting to track folk's location over
the next few months.

Columns A thru AE are data type entries....columns AF thru IU are date
ranges. I am tracking/have entered over 300 records/rows.

I have had some previous help on coding to shade the interior of the date
cells based on what text entry that I make in the cell. Unfortunately, I
didn't think of or ask for the code to return the cell interior color to
white when i deleted the text entry...so I tried working the code so that it
would. Well, it does, but only for one cell at a time. Any
ideas?????????????????

Secondly, I will enter text into the date range cells, and they will
interior shade to the correct color. What I am noticing is that the text
entries into the date range cells will then "migrate" to other (row)
cells....causing my spreadsheet to be worthless.

At first I thought that I was causing it by using the auto filter function
and then cutting and pasting the date range text entries. So I quit using
the auto filter function to enter data...I only used it to view data.

Then I would use the sort function to set up a view that worked for me, then
I would enter the data one record at a time...and it appeared that the data
was not migrating into other (row) cells.

So now that I have built formulas and graphs...I go back and take a look at
the base spreadsheet...and find that the data has migrated!!!!!!!!!!!!!!!

I am tracking over 300 people...from Feb thru Sep....and the "calendar"
spreadsheet is worthless if data keeps migrating!!!

I am not sure what is causing this!!! Is it the Excel application
itself...or is it the code that I am using to change the interior color of
cells with text entries?????

Would appreciate it if someone could look, yet again, at the coding I am
using. Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
Const colorGray40 = 48
Const colorRed = 3
Const colorBlack = 1
Const colorSeaGreen = 50
Const colorBrightGreen = 4
Const colorTurquoise = 8
Const colorYellow = 6
Const colorLavender = 39
Const colorLightOrange = 45
Const colorWhite = 2
Const colorViolet = 13

If Target.Cells.Count > 1 Then
Exit Sub
End If
Select Case UCase(Trim(Target))
Case Is = "DB"
Target.Interior.ColorIndex = colorGray40
Target.Font.ColorIndex = colorGray40
Case Is = "DN"
Target.Interior.ColorIndex = colorBrightGreen
Target.Font.ColorIndex = colorBrightGreen
Case Is = "DS"
Target.Interior.ColorIndex = colorSeaGreen
Target.Font.ColorIndex = colorSeaGreen
Case Is = "DO"
Target.Interior.ColorIndex = colorTurquoise
Target.Font.ColorIndex = colorTurquoise
Case Is = "DJ"
Target.Interior.ColorIndex = colorRed
Target.Font.ColorIndex = colorRed
Case Is = "HH"
Target.Interior.ColorIndex = colorYellow
Target.Font.ColorIndex = colorYellow
Case Is = "PCS"
Target.Interior.ColorIndex = colorViolet
Target.Font.ColorIndex = colorViolet
Case Is = "PG"
Target.Interior.ColorIndex = colorLavender
Target.Font.ColorIndex = colorLavender
Case Is = "LV"
Target.Interior.ColorIndex = 1
Target.Font.ColorIndex = 1
Case Is = "TD"
Target.Interior.ColorIndex = 45
Target.Font.ColorIndex = 45
Case Is = ""
Target.Interior.ColorIndex = 2
Case Else
'do nothing
End Select
End Sub

thanks in advance!

patrick

 

[Bernie Deitrick]

Patrick,

For the first part, change

If Target.Cells.Count > 1 Then
Exit Sub
End If

to

Dim myC As Range

If Target.Cells.Count > 1 Then
For Each myC In Target
If myC.Value = "" Then
myC.Interior.ColorIndex = 2
End If
Next myC
Exit Sub
End If

For the other part, I'm really not sure what you mean by the data migrating - there is nothing in
the code that would move values.

HTH,
Bernie
MS Excel MVP