I have the following problem:
I created a macro that locks rows for specific ranges (B20:Y20) in my excel spreadsheet and paints the locked rows with a specific color. Furthermore it creates a timestamp for the locked rows in Y20. The condition for the cell lock is that the value of column B20 is not empty.
When I activate the macro it should first be checked whether cell B20 has a value > 0. If this is the case, the row for the specified range is locked and colored and the timestamp is added.
The goal would now be to perform the identical procedure for the next row (Range B21:Y21) when B21 has a value of >0. This leads to my problem: How can I "roll forward" my macro to be performed in the continuing rows?
From my point of view the goal would be to adjust all ranks of my code so that the columns and rows become "dynamic" and cannot be executed "rigidly" for only one specific line as currently displayed.
Does anyone have an idea how my code could be adjusted in a way that by activating the macro the next rows that are >0 for column B are used for processing?
Thanks in advance.
Here's my code:
Sub Check()
If ActiveSheet.Range("B20") = "" Then
MsgBox "Please Check Input Data.", vbOKOnly, "Error"
Else
Call Savepostings
End If
End Sub
Sub protectrow()
Dim rng As Range
Set rng = Range("A20:Y20")
ActiveSheet.Unprotect Password:="A"
rng.Interior.Color = vbRed
rng.Locked = True
ActiveSheet.Protect Password:="A", DrawingObjects:=True, _
Contents:=True, Scenarios:=True, _
userinterfaceonly:=True
ActiveSheet.EnableAutoFilter = True
End Sub
Sub Savepostings()
If Cells(13, 2) = "ERROR" Then
MsgBox "Error", vbOKOnly, "Error"
Else
MsgBox "OK", vbOKOnly, "OK"
Call printtime
Call protectrow
End If
End Sub
Sub printtime()
Cells(20, 24) = printUserName
Cells(20, 25) = printTimeStamp
End Sub
Function printUserName() As String
printUserName = Application.UserName
End Function
Function printTimeStamp() As String
printTimeStamp = Now()
End Function