Changing cell color after initial input..

[rlink_23]

So I am getting very frustrated and hoping someone can help me. It seems so simple yet I cannot figure it out... I have a User form that inputs information over to a Sheet (Sheet1) in excel... Column M in Sheet1 has initials of the person that input that row from the User form. It starts out blank, then gets filled with the initials... What I want to happen is for the text to be black when first input, but if it gets edited AFTER the first data entry it turns red... I can get the cell to change color every way possible except the way I need it to change... Long story short if someone changes the cell to someone else's initials I want to know about it.. lol


Please help before I pull the rest of my hair out haha..

 

[jproffer]

Can you post the line(s) of code that puts the initials into the cell?

Seems like it would be easier to lock the cell after it's <> nothing. It would take a worksheet change event to color a cell if it was changed manually...and there wouldn't be anything stopping the user from just changing the color back to black.

Range("A1").Locked = True

adjust range accordingly

right below the user initials input line in your code would do that.

But the sheet would have to be defaulted to locked with a password (for it to matter)...unlocked before your code begins and re-locked after.

If you're dead set on the color change, the sheet will still have to be locked/unlocked/re-locked by code.

 

[rlink_23]

So the user form is designed to be able to click save and it transfers info to Sheet1. The code to do this is as follows

Private Sub CommandButton5_Click()   Dim rw As Long    'next available row
 
   With Sheets("Quotes")
 
      'get the next avialable row in Sheet1
      rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1
      'put the text box values in this row
      .Range("B" & rw).Value = Date1.Value
      .Range("C" & rw).Value = Year.Value
      .Range("F" & rw).Value = Application.Value
      .Range("G" & rw).Value = FirstName.Value
      .Range("H" & rw).Value = LastName.Value
      .Range("I" & rw).Value = Phone1.Value
      .Range("J" & rw).Value = Email.Value
      .Range("K" & rw).Value = Cost.Value
      .Range("O" & rw).Value = City.Value
      .Range("P" & rw).Value = State.Value
      .Range("Q" & rw).Value = ZipCode.Value
      .Range("M" & rw).Value = Initals.Value
   End With
 
   '================================
   'OPTIONAL - clear the text boxes
   '================================
   'Date1.Value = ""
   'Year.Value + " "
   'Application.Value = ""
   'FirstName.Value = ""
   'LastName.Value = ""
   'Phone1.Value = ""
   'Email.Value = ""
   'Cost.Value = ""
   'Initals.Value = ""
   'City.Value = ""
   'State.Value = ""
   'ZipCode.Value = ""
   'Make.Value = ""
   'Model.Value = ""
   '==========================
   'OPTIONAL - unload the form
   '==========================
   Unload Me
End Sub

But they also have the ability to click on the row, say for instance, they want to bring back up row 6 in the user form to edit it and re-save it, they highlight row 6 and it pops it back up. I tried to lock column M once that is done then you can't click on the row to pull info back up in the user form. So that is why I was thinking color change. Which I got it to change the color but it changes to the color upon first instance of data entry into the cell. So what I wanted was to have it not change color upon first entry but only if it is edited after initial entry.

 

[rlink_23]

Also forgive my crude coding skills lol, I am still learning as I go..

 

[jproffer]

I tried to lock column M once that is done then you can't click on the row to pull info back up in the user form.

When the protect sheet box pops up, you have the ability to check/uncheck what you want the user to be able to do.

Activesheet.Protect
ActiveSheet.EnableSelection = xlNoRestrictions

or some variation of that, to specify your sheet.

See if you can select it then