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
"shekpatrick" <shekpatrick[ at ]discussions.microsoft.com> wrote in message
news:6B6DB339-51BF-4D3D-9D55-0184531BA639[ at ]microsoft.com...
[Quoted Text] >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
>
|