7

I want all of the diagonal cells (A1, B2, C3, D4, E5, etc) of a Sheet to be formatted as Black Background, White Foreground. Is there any way to do this for a Sheet that constantly increases in size?

If I manually do so for the first 50 diagonal cells and the Sheet increases in size, the 51st diagonal cell will not share the same formatting. I'll have to go back and format it myself again.

Is there an easy way to go about this?

Improve this question

2 Answers 2

Reset to default
7

Automatic if within the existing formatted range (ie added rows are above and added columns are to the left) would be conditional formatting with Custom formula is 

=row()=column()

and Text Colour: white (assuming this is what you mean by foreground) and Background Colour: black applied to Range: to suit.

Improve this answer
1
  • 1
    This is exactly what I was looking for. I didn't know there were functions you could call within the Conditional Formatting field.
    – Axoren
    May 4, 2014 at 17:39
4

This cannot be done without scripting, but the script itself is quite simple:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var dataRange = sheet.getDataRange();
  var backgroundColorRows = new Array();
  var foregroundColorRows = new Array();
  var maxRow = dataRange.getLastRow();
  var maxCol = dataRange.getLastColumn();
  for (var i = 0; i < maxRow; i++) {
      var thisBackgroundRow = new Array(maxCol);
      var thisForegroundRow = new Array(maxCol);
      if (i < maxCol) {
        thisBackgroundRow[i] = "black";
        thisForegroundRow[i] = "white";
      }
      backgroundColorRows[backgroundColorRows.length] = thisBackgroundRow;
      foregroundColorRows[foregroundColorRows.length] = thisForegroundRow;
  }
  dataRange.setBackgroundColors(backgroundColorRows.splice(0, maxRow));
  dataRange.setFontColors(foregroundColorRows.splice(0, maxRow));
}

To install the script, click Tools -> Script editing, paste the code into the editor, and save. Note that the cell color will not be set until you actually enter a value into the cell.

Improve this answer
4
  • I'm having trouble getting this to work. Edit: Whoops... apparently, enter posts instead of adding new lines. I made the script and then ran it from the script manager and nothing happened. I tried entering values into the diagonal cells and nothing occurred.
    – Axoren
    Oct 20, 2012 at 1:44
  • I have updated the code. Now, every cell on the diagonal is getting background/foreground color set whenever there is an edit. Could you try this version?
    – Vidar S. Ramdal
    Oct 22, 2012 at 7:36
  • Comparing this method with the recently posted one, I'm switching the accepted answer to that one. Though your method works, it's over-complicated and is not a method I would consider when there are alternatives.
    – Axoren
    May 4, 2014 at 17:41
  • 1
    Yes, pnuts' answer is simpler. Function calls within conditional formatting wasn't available before the New Google Spreadsheets arrived.
    – Vidar S. Ramdal
    May 5, 2014 at 6:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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