Worksheet change event for range of cells and data validation

PFA sample file.

This is the workbook which I will send to users. User then will open this file and update field and click on send mail button to send back workbook to me.
I have assigned Send mail macro to command button. This macro will send mail back to me once user updates all fields and click on this button.

Before clicking on command button I want users to update columns Status(Column F), Review (Column H) and Effective Inactive Date(Column G). Please not that updating values of Effective Inactive Date are mandatory only if user enters value Inactive in adjancent column 'Stasus'.

I have done data validation for Status column(allows only Active or Inactive value) and Review column(allows only Yes or No values). Please see that.

Please check below my requirements:
1. Command button must be disabled if user has not updated required fiels. Required fiels are Status Column and Review Column

1. Column F:Status Column -->
a. If user selects Inactive value then message box should pop up asking user to enter Effective Inactive Date value in adjacent column H(Effective Inactive Date column)

b.And if user has not updated Effective Date column then again command button should not work. This means Effective Inactive values should be mandatory if user enters status as Inactive in Status column.

c. If field in Status column is left blank then Command button(here SendEmail) button should not work and one message box should pop up upon clicking command button asking user to update require fields


2. Column H: Review Column -->
a. If user enters value in this column as No then again command button should not work. One message box should pop up asking user to review required field again.



Please let me know in case any further information is required. And guys please help me to solve this problem as I am new to Worksheet_change event.

hi santosh123, option to make it, please check attachment, press send Mail

123_sample_file(11).xls - without Conditional Format being used
123_sample_file(1).xls with Conditional Format being used

Thanks Watersev for your gr8 help.
I am very thankful for that.
the 1st file with conditional format resolved my issue.
Could you please also provide macro for conditional format. I am not able to do this on my own.
Once again thanx for youy great help.

code for conditional format:

Please Login or Register  to view this content.

Thanks Watersev. Thanks for your gr8 help.