To protect and lock cells after entry of weight in col. A, time of data entry will be recorded, comapare value, and set counter?

[TKKal]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tasksheet As Worksheet
Dim i As Integer
Dim T2 As Integer
Dim Lr As Integer

ActiveSheet.Unprotect Password:="123"
Target.Locked = False

Set tasksheet = ThisWorkbook.Sheets("Sheet1")
Lr = tasksheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lr
If tasksheet.Cells(i, "A").Value <> "" And tasksheet.Cells(i, "B").Value = "" Then

tasksheet.Cells(i, "B").Value = Time
tasksheet.Cells(i, "B").NumberFormat = "hh:mm"
tasksheet.Cells(i, "A").Font.ColorIndex = 5
End If
If tasksheet.Cells(i, "A") < 18.9 Then
T2 = T2 + 1
tasksheet.Cells(i, "A").Font.ColorIndex = 3
End If

Next

Cells(5, 10).Value = T2

ActiveSheet.Protect Password:="123"


End Sub

Whenever I ran the code, I receive a run time error 1004, on line (tasksheet.Cells(i, "B").NumberFormat = "hh:mm"). If password protection line is removed the code run with no problem

 

[DanteAmor]

Hi and welcome to MrExcel!

The sheet where the code is and the sheet "Sheet1" are different sheets?
Which sheet is the one with the password?

You want the event to be triggered when you modify a cell in a specific range, that is, for example, that the event is triggered when you modify a cell in column A.
Or in any cell on sheet?

You can explain a little, what do you want to do with the macro.

 

[TKKal]

Hello
The sheet I need to protect is sheet1,
To summarize What I need
1- Enter Data in Column A,
2- Time should be recorded in Column B
3- Cells must be locked after data entry by password ( Cells value can not be change without password)
4- If the value in Column A is less than (18.9) red font applied and counter is set
The problem this code is running fine as long as there is no sheet protection, once sheet protection line is applied, I receive Error 1004 (Run time).

 

[DanteAmor]

The sheet where the code is and the sheet "Sheet1" are different sheets?

Please answer.

 

[TKKal]

Hi
No same sheet, the code is in sheet1

 

[DanteAmor]

I'm sorry, but I didn't understand your answer. Is the code and sheet1 the same sheet?

And another question, why do you need the For loop?

 

[DanteAmor]

Try this
Add EnableEvents instruction

Private Sub Worksheet_Change(ByVal Target As Range)

Dim tasksheet As Worksheet
Dim i As Integer
Dim T2 As Integer
Dim Lr As Integer
application.EnableEvents = false
ActiveSheet.Unprotect Password:="123"
Target.Locked = False

Set tasksheet = ThisWorkbook.Sheets("Sheet1")
Lr = tasksheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To Lr
If tasksheet.Cells(i, "A").Value <> "" And tasksheet.Cells(i, "B").Value = "" Then

tasksheet.Cells(i, "B").Value = Time
tasksheet.Cells(i, "B").NumberFormat = "hh:mm"
tasksheet.Cells(i, "A").Font.ColorIndex = 5
End If
If tasksheet.Cells(i, "A") < 18.9 Then
T2 = T2 + 1
tasksheet.Cells(i, "A").Font.ColorIndex = 3
End If

Next

Cells(5, 10).Value = T2
Application.EnableEvents = true
ActiveSheet.Protect Password:="123"


End Sub

 

[TKKal]

Thank you very much, now it is working perfectly

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.