Format Cell Based on the Data Validation Value-CodePudding

I would like to format a cell to percentage based on the value of the data validation from another cell. Eg. A1 has a data validation of [Number,Percent]. If the user chose "Percent", A2 cell will be formatted to percentage.

Here's a scenario, A2 value = 15. When the user chose Number in A1, A2 value will be the same whereas if the user chose Percent, the value of A2 will become 15%

I tried to search for it online but I cannot find any reference.

CodePudding user response：

Set To Percentage

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "Your sheet name" && e.range.columnStart == 1 && e.range.rowStart == 1 && e.value == "Percentage") {
    sh.getRange("A2").setNumberFormat("0.00%");
  }
  if(sh.getName() == "Your sheet name" && e.range.columnStart == 1 && e.range.rowStart == 1 && e.value != "Percentage") {
    sh.getRange("A2").setNumberFormat("0.################");
  }
}

Demo: