VBA code for locking and unlocking cells depending on another cells value

[KarenVFL]

Hi All

I have never used VBA before and am finding it a complete mind field!

I need a VBA code that will lock/unlock a selection of cells depending on the value of another cell.

Basically if the value of cell O15 is greater than 04:30 (4 and a half hours in time) then it unlocks cell range Q8:Q14 and turns the cells from filled Black in colour to filled White, but if the value of cell O15 is less than 04:30 (4 and a half hours in time) then cell range Q8:Q14 must stay locked and stay filled black.

Is anyone able to assist with this?
Many thanks in advance

[Paul]

Is the value in O15 based on a formula, or manually entered? That will determine if you use a Worksheet_Calculate or Worksheet_Change event. I've shown the Calculate event below.

Please Login or Register  to view this content.

To add this code, right-click the sheet tab and select View Code. Paste this code into the VB Editor window for that sheet and then close the VB Editor. Whenever the worksheet calculates, it will check cell O15 and adjust Q8:Q14 accordingly.

If you are using manual entry for O15, the code would be more like:

Please Login or Register  to view this content.

Note: 0.1875 is the numeric equivalent to 4:30am based on # of seconds elapsed in the day (16200) divided by total # of seconds in a day (86400).

[KarenVFL]

Hi Paul

Thank you for the code, it does work, however I'm now thinking it may just be easier to hide the whole of column Q, but unhide it if O15 is greater than 4:30.
Is this possible to do?

[Paul]

Yes, just replace the "Range("Q8:Q14")...." bits with

Range("Q:Q").EntireColumn.Hidden = True (to hide the column)

or

Range("Q:Q").EntireColumn.Hidden = False (to show the column).