Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data entered in one record is shifting to others
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data entered in one record is shifting to others
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shifting Data Points | Charts and Charting in Excel | |||
how do I record the time data is entered into a spreadsheet? | Excel Worksheet Functions | |||
Shifting Date from all data on one row to several rows | Excel Discussion (Misc queries) | |||
Automatically shifting data, part II | Excel Worksheet Functions | |||
recording the date when record was entered in cell in Excel | Excel Worksheet Functions |