How to clear cell when enter invalid data in Excel using VBA -

i have excel sheet in accepting value user when user enter value vba run check data valid or not , if data not valid prompt message saying invalid data. here code:

    private sub worksheet_change(byval target range)  if target.address = "$d$12" or target.address = "$d$13" or target.address = "$d$14" or target.address = "$d$15" call room end if end sub  

room method

sub room() dim lastrow long if isnumeric(range("i17")) if [i17] < 0  msgbox "msg " end if end if end sub 

in i17 cell have formula

=c6-(d12+(2*d13) + (2*d14) + (3*d15)) 

my problem when wrong data enter in of cells (d12, d13, d14, d15) cell should clear automatically after showing prompt message.

how can done?

the first thing should clean how check target is. multiple cells (fill down, paste range, ...). accomplished intersecting target range interested in, , we'll store range variable, later. if there no overlap, intersect return empty object, can test is nothing.

the next thing note odd things (infinite recursion) can happen if allow worksheet_change event fire changing cell. prevent this, turn off events before calling room, , turn on after we're done.

next pass range has changed room, can modify within subroutine.

and, modify affected range after displaying message. note have used command literally clear cell. since performing calculations based on data, might prefer set default value, 0, using a.value = 0 instead.

private sub worksheet_change(byval target range)     dim range      set = intersect(target, range("d12:d15"))      if not nothing         application.enableevents = false         room         application.enableevents = true     end if end sub  sub room(a range)     dim lastrow long     if isnumeric(range("i17"))         if range("i17").value < 0             msgbox "msg "             a.clearcontents         end if     end if end sub 

as side note, have used bad variable name a, since don't know range represents. should pick describes future maintainers going on.