0

Is it possible to use conditional formatting to edit/add text in a cell? E.g., if it matches the custom formula "=B9>A9", add "is greater than A9" as text at end of whatever is in B9. I didn't find any method to add custom formatting, all I'm seeing are colour/font formatting options.

1

Conditional formatting can only be used to change text and/or background formatting.

See: Google Support: Use conditional formatting rules in Google Sheets

| improve this answer | |
0

While in Excel is possible to use Conditional Formatting to apply conditional numbering formatting including custom text, in Google Sheets is not possible at this time, so a script is needed. To learn the basics of Google Apps Script see https://developers.google.com/apps-script/overview.

The following code adds is greater than A9 to the value of B9 and only to the value of B9 whenever the value of B9 is modified and it's greater than the value of A9:

function onEdit(e) {
  var range = SpreadsheetApp.getActiveRange();
  var value = range.getValue();
  var reference = range.getA1Notation();
  if (reference == 'B9') {  
    var A9 = SpreadsheetApp.getActiveSheet().getRange('A9').getValue();
    if (typeof value == 'number' && value > A9) {
      range.setValue(value + ' is greater than A9');
    } 
  }
}
| improve this answer | |
-2

You would have to use AppsSCript I'm pretty sure. as Long as you have predictable data entering the data you can move it around and do just about anything. if you've never used AppsScript you can open the script editor under the Tools> Script editor selection. I recently just did a little snippet to take the value entered in 6 digit format and use that to change the backround and font color the coresponding color in Hex. Maybe this will help, I'm only a begginer myself. Cheers. I made a beginers Tutorial Here that may help.

       function onedit() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var s = ss.getActiveSheet();
      var r = s.getActiveRange();
      var row = r.getRow();
      var hex = r.getValue();
      if(row>20){
      r.setBackground('#'+hex).setFontColor('#'+ hex);
      }
    {
// EDIT: this code is for generating urls for prefilled google forms.
// it also changes content of cells depending on a few variables 
// it is triggered via OnEdit() in AppsScript on a Google Sheet

function SampleFormUrl(U){
  var colIndexU = U.range.getColumn();
  var rowIndexU = U.range.getRow();
  var daysSince = s.getRange(rowIndexU,25);
  if( U.value == "Arrived" && colIndexU == sampleCol ){ 
    daysSince.clear();
  }else
  {  if( U.value == "Send" && colIndexU == sampleCol ){ 
    var formlinkCell = s.getRange(rowIndexU,26);
    var forminfo = s.getRange(rowIndexU,5,1,7);
    var formSheet = ss.getSheetByName("Data");
    var formInput = formSheet.getRange(11,2,1,9);
    forminfo.copyValuesToRange(formSheet, 2, 9, 12, 12);
    var formUrlRange = formSheet.getRange(6,1);
    var formUrlCell = formUrlRange.getCell(1,1).getValue();
    formlinkCell.setValue(formUrlCell);
    U.range.setValue("Sent");
    daysSince.setValue(0);
  }
}
}
| improve this answer | |
  • You are right regarding that a script is required but the question is about adding changing the value of a cell when a condition is met. – Rubén Sep 1 '16 at 12:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.