Here is a copy of the document I'm working on: https://docs.google.com/spreadsheets/d/1amIjjQsA8sNKf7mZvE1GjsF-377YOWxVIRk6HRgQjfA/edit?usp=sharing

I'd like the drop down menu (in purple--D3-L3) to modify a range of cells depending on the selection. Here is a chart of my ideal results (data validation selection is in bold followed by intended results):

2hr Delay clear cell values and grey out-- rows 4-18--no data collected.

Absent clear cell values and grey out-- rows 4-18--no data collected.

Activity auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Appointment clear cell values and grey out-- rows 4-18--no data collected Counseling Room autopopulate green cells (rows 4-8) with 0 preserving the "sum" function.

Early Dismissal clear cell values and grey out-- rows 4-18--no data collected.

Field Trip auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Equine Asst. Learning auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

In Class auto populate green cells (rows 4-8) to blank preserving the "sum" function.

In-School Intervention autopopulate green cells (rows 4-8) with 1 preserving the "sum" function.

Meeting auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Therapy auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

I've gotten this far on my own. At best I could get it to grey out the cells, but it would also clear the formatting. If a drop down selection was reverted back to a different selection, the formatting did not return.

function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet() var s=ss.getActiveSheet() var nota=e.range.getA1Notation() if(nota=="D3"){ var stat=e.range.getValue() if(stat=="2hr Delay", 'Absent', 'Appointment', 'Early Dismissal'){ s.getRange("D4:D18").clearContent(null) s.getRange("D4:D18").clearContent("D4:D18"); } else{ s.getRange("D4:D18").clearContent("D4:D18");
var cell = s.getRange("D4:D18"); }}}

Here is a copy of the document I'm working on: https://docs.google.com/spreadsheets/d/1amIjjQsA8sNKf7mZvE1GjsF-377YOWxVIRk6HRgQjfA/edit?usp=sharing

I'd like the drop down menu (in purple--D3-L3) to modify a range of cells depending on the selection. Here is a chart of my ideal results (data validation selection is in bold followed by intended results):

2hr Delay clear cell values and grey out-- rows 4-18--no data collected.

Absent clear cell values and grey out-- rows 4-18--no data collected.

Activity auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Appointment clear cell values and grey out-- rows 4-18--no data collected Counseling Room autopopulate green cells (rows 4-8) with 0 preserving the "sum" function.

Early Dismissal clear cell values and grey out-- rows 4-18--no data collected.

Field Trip auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Equine Asst. Learning auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

In Class auto populate green cells (rows 4-8) to blank preserving the "sum" function.

In-School Intervention autopopulate green cells (rows 4-8) with 1 preserving the "sum" function.

Meeting auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

Therapy auto populate green cells (rows 4-8) with 2 preserving the "sum" function.

I've gotten this far on my own. At best I could get it to grey out the cells, but it would also clear the formatting. If a drop down selection was reverted back to a different selection, the formatting did not return.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var nota=e.range.getA1Notation()
  if(nota=="D3"){
  var stat=e.range.getValue()
    if(stat=="2hr Delay", 'Absent', 'Appointment', 'Early Dismissal'){
      s.getRange("D4:D18").clearContent(null)
      s.getRange("D4:D18").clearContent("D4:D18");
  }
      else{
        s.getRange("D4:D18").clearContent("D4:D18");      
        var cell = s.getRange("D4:D18");
   }}}