Conditional formatting dependant of value from adjacent cell

Question

I set up a time recording sheet in Google Spreadsheets. Now I am trying to achieve a conditional formatting of cells depending on the value of other cells in the spreadsheet:

IF value from cell G2 is greater than 06:30:00 (hh:mm:ss) AND value from cell D2 is smaller than 00:30:00 (hh:mm:ss) OR cell D2 is blank then set background colour of cell D2 to yellow.

IF value from cell G2 is greater than 08:45:00 (hh:mm:ss) AND value from cell D2 is smaller than 00:45:00 (hh:mm:ss) OR cell D2 is blank then set background colour of cell D2 to yellow.

If the conditions aren't met the background colour should be reset to none.

I learned that it is not possible using formulas. Is there an approach doing it in Google Apps Script? I am no scripter but know how to insert snippets and may be able to read and understand if someone would be so nice to explain it to me.

When searching for similar use cases the only reference to Google Apps script I found was this link, a general discussion of its capabilities.

As far as I know, this is not possible with Google Fusion Tables. You can't format a cell. You probably want to do this with Google Spreadsheet. — Odi
– Odi, Commented May 26, 2012 at 6:29
Ok, thank you. I switched everything to Google Spreadsheets. How can I do it there? — user1417052
– user1417052, Commented May 28, 2012 at 10:23

Eric Koleda · Accepted Answer · 2012-05-31 22:25:19Z

This should be possible by creating an onEdit trigger for a function like this:

function myOnEdit(e) { var nextColumnCell = e.range.offset(0, 1); if (e.range.getValue() == 'foo') { nextColumnCell.setFontWeight('bold'); } else { nextColumnCell.setFontWeight('normal'); } }

More information on triggers can be found here.

Eric: Thank you very much for your contribution. I'll check and let you know when I am done. Charles: Thanks for editing the link and adding the google-apps-script tag.

jonan.pineda · Accepted Answer · 2014-01-23 07:08:23Z

Somehow, I can't also find for a way to directly compare time in Google Spreadsheet.. But I it is possible to convert from time to number format. By doing so, you will be able to compare the entities.

1 in number format is equal to 00:00:00 in time format because 1 stands for 12:00am

0.5 in number format is equal to 12:00:00 in time format because 0.5 stands for 12:00pm

0.25 in number format is equal to 6:00:00 in time format because 0.5 stands for 6:00am

It doesn't answer the main question but I hope it helps.

pnuts · Accepted Answer · 2017-04-23 21:04:20Z

It is (now) possible with a formula.

Select ColumnD and clear any existing CF rules from it. Format, Conditional formatting..., Format cells if... Custom formula is and

=and(row()<>1,or(and(G1>6.5/24,D1<0.5/24),and(G1>8.75/24,D1<0.75/24)))

with yellow fill and Done.

Collectives™ on Stack Overflow

Conditional formatting dependant of value from adjacent cell

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related