If one cell for each row from range A1:D50 is checked or has value>0, lock other cells in the row

[vywes]

Never	Per month	Per week	Per day	Total
[/]	value	value	value

How do I write the vba such that

1. if one cell for each row from range A2:D50 is checked or has value>0, lock other cells in the row.
2. And cell from range E2:E50 (Total) =

• =0 if checkbox is checked
• OR =cell B2.value divide 30 if cell B2.value>0
• OR =cell C2.value divide 7 if cell C2.value>0
• OR =cell D2.value if cell D2.value>0

I wrote the code like this but ran into error

Sub Frequency()

Dim rng As Range, cell As Range
Set rng = Range("C14:F16")

Dim rng0 As Range, cell0 As Range, CheckBox As Object
Set rng0 = Range("C14:C16")

Dim rng1 As Range, cell1 As Range
Set rng1 = Range("D14:D16")

Dim rng2 As Range, cell2 As Range
Set rng2 = Range("E14:E16")

Dim rng3 As Range, cell3 As Range
Set rng3 = Range("F14:F16")

For Each cell In rng
    If Range("rng0").Object("CheckBox").Value = True Then
        cell0.Offset(0, 10).Value = 0
    ElseIf Range("rng1").cell1.Value >= 1 Then
        cell1.Offset(0, 9).Value = cell1.Value / 30
    ElseIf Range("rng2").cell2.Value >= 1 Then
        cell2.Offset(0, 8).Value = cell2.Value / 7
    ElseIf Range("rng3").cell3.Value >= 1 Then
        cell3.Offset(0, 7).Value = cell3.Value
    End If
    
Next cell

End Sub

 

[rakesh seebaruth]

try thia

Private Sub Worksheet_Change(ByVal Target As Range) For Each cell In Range("A2:D50")  If cell.Value > 0 Or cell.Value = True Then     Range("A2:D50").Locked = True  Else     Range("A2:D50").Locked = False  End If Next cell End Sub To write the code to calculate the values in the range E2:E50, you can use the following code: Private Sub Worksheet_Change(ByVal Target As Range) For Each cell In Range("A2:D50")  If cell.Value > 0 Then    If cell.Address = "B2" Then      Range("E2").Value = cell.Value / 30    ElseIf cell.Address = "C2" Then      Range("E2").Value = cell.Value / 7    ElseIf cell.Address = "D2" Then      Range("E2").Value = cell.Value    Else      Range("E2").Value = 0    End If  Else    Range("E2").Value = 0  End If Next cell End Sub.

 

[vywes]

try thia

Private Sub Worksheet_Change(ByVal Target As Range) For Each cell In Range("A2:D50")  If cell.Value > 0 Or cell.Value = True Then     Range("A2:D50").Locked = True  Else     Range("A2:D50").Locked = False  End If Next cell End Sub To write the code to calculate the values in the range E2:E50, you can use the following code: Private Sub Worksheet_Change(ByVal Target As Range) For Each cell In Range("A2:D50")  If cell.Value > 0 Then    If cell.Address = "B2" Then      Range("E2").Value = cell.Value / 30    ElseIf cell.Address = "C2" Then      Range("E2").Value = cell.Value / 7    ElseIf cell.Address = "D2" Then      Range("E2").Value = cell.Value    Else      Range("E2").Value = 0    End If  Else    Range("E2").Value = 0  End If Next cell End Sub.

When i wrote it as Private Sub Worksheet_Change(ByVal Target As Range) I have trouble stepping into the code.

So I tried to write it this way:

Sub Frequency1()

Dim rng As Range, cell As Range
Set rng = Range("B14:E16")

For Each cell In rng
    If cell.Value > 0 Or cell.Value = True Then
        Range("rng").Locked = True
    Else: Range("rng").Locked = False
End If
Next cell

End Sub

However, I ran into application-defined or object-defined error 1004 at [Range("rng").Locked = False]

 

[Joe4]

What is "rng"?

Shouldn't that be:

cell.Locked = True

instead?