excel - Force pasted values to obey data validation rules -

i have (simplified example) matrix consisting of inputs , alarms. each action (x) should have input , alarm i.e. no actions should inserted in column e or row 6.

i used data validation implement , works.

however if paste data these cells not follow validation rules. inserted vba code prevent (extracted www.j-walk.com/ss/excel/tips/tip98.htm):

private sub worksheet_change(byval target range)     'does validation range still have validation?     if hasvalidation(range("validationrange"))         exit sub     else        application.enableevents = false        application.undo         msgbox "your last operation canceled." & _         " have deleted data validation rules.", vbcritical     end if end sub  private function hasvalidation(r) boolean '   returns true if every cell in range r uses data validation     on error resume next     x = r.validation.type     if err.number = 0 hasvalidation = true else hasvalidation = false end function 

however, code prevents values being pasted cells when not break validation rules e.g. if paste x input a;alarm 1, error message. there way prevent values being pasted when break validation rules?

edit:

i have altered code to:

private sub worksheet_change(byval target range)  range("d4:h8").validation         .delete         .add type:=xlvalidatecustom, alertstyle:=xlvalidalertstop, operator:= _         xlbetween, formula1:="=or(isblank(d4),and(not(isblank($c4)),not(isblank(d$3))))"         .ignoreblank = false         .incelldropdown = true         .inputtitle = ""         .errortitle = "stop"         .inputmessage = ""         .errormessage = "actions must have input , output"         .showinput = true         .showerror = true     end  me.circleinvalid  count = 0 dim shp shape each shp in activesheet.shapes     if not intersect(shp.topleftcell, range("d4:h8")) nothing count = count + 1 next  if count > 0 msgbox "actions must have input , output" end if  end sub 

this circles invalid cells , produces msg box if 1 found. done based on fact invalid circle shape. can code work searching whole sheet trying narrow search specified range. error "1004 - application-defined or object-defined error" due shp.topleftcell. ideas?

decided not go down shape route , instead search validation values:

dim cell range each cell in range("d4:h8")     if not cell.validation.value         msgbox "actions should have input , output"         exit sub     end if next