VBA code required

[RPT_22]

Hi

I use this formula in B27
= ((B20/18.4)*B22)-B22

Is there code which could perform the following?
Code to give error message for 1 & 2
1. If the value entered into B20 is 18.4 or less - Message "Value must be between 18.5 & 80"
2. If the value entered into B22 is less than 500 or greater than 1000 - Message "Value must be between 500 & 1000"
3. If value entered in either B20 & B22 is incorrect then make cell B27 invisible until value is correct in both B20 & B22
4. Code to lock cell B27 so formaula cannot be erased

Thank you

 

[GWteB]

You don't need VBA for what you're after. You can add Data Validation to both cells B20 & B22, unlock those cells and protect your worksheet.

 

[RPT_22]

Thanks
I would prefer VBA code for this if it`s possible
Thanks again

 

[pjmorris]

its possible, but why would you? To distribute it you'd need to send .xlsm files which some email servers dont like (depends on company policies). Steps 1 & 2 are Data Validation rules, Step 3 is conditional formating and Step 4 is protection of your worksheet as mentioned above. Using VBA to do these tasks is not a good use of VBA as it becomes more difficult to maintain.

 

[RPT_22]

Hi
Thanks
How would you set 1 & 2 with DV, 3 with conditional formatting
Thanks

 

[GWteB]

Regarding your topic there are multiple videos on the internet. For a start take a look over here ...

Apply data validation to cells - Microsoft Support

Use data validation rules to control the type of data or the values that users enter into a cell. One example of validation is a drop-down list (also called a drop-down box or drop-down menu). Watch more in this video.

support.microsoft.com

 

[pjmorris]

Options 1 & 2 are self evident when you click on either cell and then choose 'Data Validation' from the Data ribbon.

You can also use Data Validation to prevent data entry in B27 until the others are correct:
1. Click on B27,
2. Click on Data Validation on the Data ribbon.
3. In the 'Allow' box choose 'Custom'.
4. In the Formula box enter: =and(B20>=18.5, B20<=80, B22>=500, B22<=1000) set the error message to something like: 'Unable to enter data here unto 18.5<=B20<=80 and 500<=B22<=1000. Please check those cells and try again'

A Conditional Formatting formula such as =OR(B20<18.5, B20>80, B22<500, B22>1000) can also be used for B27, and then set the format such that the font colour matches the background when any of those conditions is true.

HTH