Locking/ Unlocking cell based on the color of the cell itself

[ialadam]

Hello Everyone,

I want to Lock/ Unlock cells in a sheet based on the filling colour of the cell itself keeping in mind that the sheet is protected.

For example, if the cell colour is back (based on defined conditional formatting) I want to the cell value to rest to "blank" or zero and to get locked. On the other hand if the cell colour changes to any other colour because the condition is not valid anymore it will get unlocked.

in the attached excel sheet , I'm targeting the cells in row I,J, and K.

I really appreciate you help
Love and peace

 

[vletm]

ialadam
the filling colour of the cell could be different than conditional formatting color.
Of course, it could use cells conditional format rules to know needed action; [un]lock .
... but, it would need macros.
You should upload a sample Excel file with needed actions
-- as written in

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

How to get the Best Results at Chandoo.org

 

[ialadam]

Thank you for the input. I attached the sample file in the my original post.
The challenge im facing is not in conditional formatting, it is in ul/locking the targeted cells

 

[vletm]

ialadam
One sample, which could work for You.
I didn't test with Your or any others protection settings.

 

[ialadam]

ialadam
One sample, which could work for You.
I didn't test with Your or any others protection settings.

Heyy,,,, I was expecting something with VB but this almost works perfectly.

May I ask how did you do it because i want to apply it on my original file?

Thanks a lot

 

[vletm]

ialadam
almost ...? what is missing?

How to apply it ...
a) Use that file
b) There are two codes in Sheet1's -code page. Copy those to same place to Your file. File have to be saved as .xlsb.

 

[ialadam]

Thanks again really for your help. I said almost just for one simple reason, which is that after looking the cells, it does not clear it is value. Not an issue because i will find away to ignore the numbers in these cell in my calculation.

Thanks a million

 

[vletm]

ialadam
... it does not clear its value ...
If there are values before that ... then it won't do so.
But if someway You try to modify those 'black cells' it would undo Your modifications.
Do You need that 'clear black cells' -feature?
... if You need ... then run manually 'Clear_Black_Cells'-macro

 

[ialadam]

ialadam
... it does not clear its value ...
If there are values before that ... then it won't do so.
But if someway You try to modify those 'black cells' it would undo Your modifications.
Do You need that 'clear black cells' -feature?
... if You need ... then run manually 'Clear_Black_Cells'-macro

That is amazing... you are awesome... Thanks a lot