change the rows value based on conditions
 

hi,

please see the comment in attached sheet for clarifcation and work in progress.

my question in comment is

how can I change this formula to do the following:

1) if the value is not anything except "Y","y", "n","N", or blank it should through a warning message. Is using list box is alternative.

2) I want this cell to actually change color if the value of the cell F4 is either Yes or Y. also i should become must to fill this cell in this case.

anyideas how to acheive this?