Prevent Users from Changing Value of a Cell if Condition is Met

Hey FallingDown,

I would use the Worsheet_SelectionChange and Worksheet_Change functions as below:

Please Login or Register  to view this content.

Obviously change the "ABC" criteria for your own, and that the column references 2 and "B" are changed for your required column.

Actual sheet protection would be better though due to the fact that VBA can be broken using Esc or Ctrl+Break (both of which can be ignored in code, but just in case of infinite loops, it's best not to)

Edit Sorry, should have explained the code really. Basically the SelectionChange function fires whenever the user selects a cell on the sheet where the code is, when this happens, the value in B22 (or whatever row) is checked to see if it meets your "ABC" criteria and, if so, stores the current value of the cell in a public variable. Then, if the user changes that cells value and the criteria in B22 is still "ABC", it will replace what value they type with the original value form the public variable.