Locking cells dependant on value in another cell

I would like to lock cells in a spreadsheet against editing dependant on a value in another cell.  Here is what I have:
           Col A      Col B
R1      Date 1    Value1
R2      Date 2    Value2
R3      Date 3    Value4
         etc etc

Basically I want to lock the changing of values in Column B if the corresponding date in Column A is before TODAY().

Is there any way that I can do this?

I would prefer to use basic Excel functions rather than macro/VB if possible.

Regards

Richard
rltomalinAsked:
Who is Participating?
 
jppintoConnect With a Mentor Commented:
Here's the code to put on your sheet. I've attached a sample file. Hope that you understand the logics here.

jppinto
Private Sub Worksheet_Change(ByVal Target As Range)

Dim cll As Range
Dim rng As Range
Set rng = Range("A1:A10")

ActiveSheet.Unprotect Password:="pass"

    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
        For Each cll In rng
            If cll.Value < Now() Then
                cll.Offset(0, 1).Locked = True
            Else
                cll.Offset(0, 1).Locked = False
            End If
        Next
    End If
ActiveSheet.Protect Password:="pass"

End Sub

Open in new window

ProtectColumn.xlsm
0
 
jppintoCommented:
You will need a macro to do that! You need to protect your sheet so that you can protect thoose cells. Basically it needs to check the value of cells in column A and compare it with today's date and if they are before today, lock the cell on column B, if they aren't, unlock the value on column B. This needs to be done using a macro!

jppinto
0
 
rltomalinAuthor Commented:
Thanks jppinto

OK - could you suggest how I do that - I'm not so familiar with creating and using macros.

Regards

Richard
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
jppintoCommented:
I've putted range A1:A10 for testing purpose but you should change it to check all of column A like this:

Set rng = Range("A:A")

and you can change also this line:

If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

for this:

If Not Intersect(Target, rng) Is Nothing Then

jppinto
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
You can use data validation to achieve this. See attached file

Saqib
Data-validation-.xls
0
 
jppintoCommented:
Saqib, I like your solution also but it's easy to write on the values on column B, they aren't really locked, you just need to copy a value from another cell on top of the value that should be locked and there it goes the Data Validation protection! To get a full proof solution, you will need to lock the cells by protecting your sheet...

jppinto
0
 
rltomalinAuthor Commented:
Thanks for the suggestions.  Have a meeting now - so will look at this later - probably tomorrow.

Regards

Richard
0
 
rltomalinAuthor Commented:
Thanks both of you for your suggestions.  While I appreciate the shortcomings of the validation solution, it would be sufficient for my purposes.
However, the formula in the sample references an absolute cell.  I have 365 daya to assign this to and I was hoping to somehow simply replicate the settings for the whole column, using a relative reference (ie the date of that cell).  Is there a way that I can do that - or do I need to modify the validation rule each time for each cell in the column.

Thanks again
Richard
0
 
Saqib Husain, SyedEngineerCommented:
The cell C1 is dependent on B1
The cell C2 is dependent on B2
The cell C3 is dependent on B3

and will continue if you copy down. There is no absolute cell referred.

Saqib
0
 
rltomalinAuthor Commented:
How right you are!!  I was sure that I copied the cells down and the reference in the validation formula stayed the same - but I must have been mistaken because I just tried it again and it is fine.
Regards

Richard
0
 
rltomalinAuthor Commented:
Although I used the validation solution, I accept that the macro is a more complete and robust solution.  Both solutions were given promptly, so I have shared the points equally.
Regards
Richard
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.