# How to freeze a cell on a specific condition

#### aashwinjain

##### New Member
Hi there !!

Could someone please help me with this.

Say i have two cell

A1 and A4

If a specific condtion is met in A1 { Say if value of a1 =3 } then A4 should freeze and should not exept any value.

Help will be much appreciated. Thank you

#### Smitty

##### MrExcel MVP
Welcome to the Board!

How is A1 changing? Via formula or manually?

#### aashwinjain

##### New Member
Welcome to the Board!

How is A1 changing? Via formula or manually?
A1 is changing via formula

#### Smitty

##### MrExcel MVP
OK, then you need to use a Calculate event:

Code:
``````Private Sub Worksheet_Calculate()
If Range("A1").Value = 3 then
ActiveSheet.Unprotect
Range("A4").Locked = True
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
Range("A4").Locked = False
ActiveSheet.Protect
End If

End Sub``````

#### aashwinjain

##### New Member
OK, then you need to use a Calculate event:

Code:
``````Private Sub Worksheet_Calculate()
If Range("A1").Value = 3 then
ActiveSheet.Unprotect
Range("A4").Locked = True
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
Range("A4").Locked = False
ActiveSheet.Protect
End If

End Sub``````

Hey Smitty : Greta.. thanx for the quick response

Couple of queries : Would be great if you could sort them as well :

• Is it possible to make the value a variable instead of fixing it to 3 { Value of a1 could be variable depending on the out come of the formula : So say if A1's outcome based on formula is either 3,6,9 then we freeze the cell else we dont }
• How to we implement this on cell A2. How do we add this macro for cell a2
Thanx in advance

#### Smitty

##### MrExcel MVP
If Range("A1").Value = 3 Or If Range("A1").Value = 6 Or If Range("A1").Value = 9 Then

Any more than that and you probably want to use a Select Case structure.

As for adding A2, will it be affecting another cell or A4 as well?

#### aashwinjain

##### New Member
Thanx for updating :

the point which is not clear is : How do i implement this.

Say i want to freeze A4 depending on the value of A1. Now that you have given me the code. Should i create a macro { If yes then what next }. Please guide though this

#### Smitty

##### MrExcel MVP
Right-click on the sheet tab where you want this to take place and select View Code. Paste the code in the new window that opens, then Alt+Q to exit back to Excel. The code will fire automatically when the sheet calculates.

#### aashwinjain

##### New Member
Right-click on the sheet tab where you want this to take place and select View Code. Paste the code in the new window that opens, then Alt+Q to exit back to Excel. The code will fire automatically when the sheet calculates.
Thanx Smitty : WOrks perfectly well :

Just the last set of questions :

• Can i customize the error message that i get when i try to enter data in freezed cell
• also when the cell is freezed based on the condition can i change the cell color to grey

Thank you and really great full for the help

#### aashwinjain

##### New Member
Also once the condition in A1 is met { Say a1=3} and a4 gets freezed out then we are unable to change the vaule of any cell. We get the message saying the file is protected. Please unprotect

Threads
1,081,524
Messages
5,359,269
Members
400,523
Latest member
ExcelNewbie98

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...