Google-sheets – Conditional formatting based on another cell

conditional formattinggoogle sheets

I use Google Sheets to schedule office appointments.

I have set conditional formatting so that if the appointment time slot cells are empty, they are highlighted yellow. (This makes it easier to find the next free appointment). When those cells contain text, they are returned to white.

However, there some days where a client has a choice between either a morning appointment or an afternoon appointment. If they choose morning, we cancel the afternoon session. If they choose afternoon, we do not have a morning session.

How do I set it up so that if B4 and B5 are empty, they are highlighted yellow, however, if B4 contains ANY text, then B5 is back to white?

Best Answer

In Conditional Formatting, select Custom Formula is and use the formula:

=AND(ISBLANK(B4),ISBLANK(B5))

Then when both B4 and B5 are empty, they will both be coloured; when either one has text in it, they will both be white.

Related Solutions

Google-sheets – Conditional formatting based on cell values in same column

Assuming the actual finishing date is in Column F and the sheet is called Sheet1, paste the code below inside your spreadsheet under Tools >> Script editor, then hit Save.

/***
* Color Code+ 
* Conditional formatting with cell referencing for Google Spreadsheets
* Support: scripts@bryanp.com
**/

function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName('Sheet1');
var values1Rule1 = s.getRange('E1:E').getValues();
var values2Rule1 = s.getRange('F1:F').getValues();
var range3Rule1 = s.getRange('E1:E');
var color1 = 'red';

for (var row in values1Rule1) {
for (var col in values1Rule1[row]) {
if (values1Rule1[row][col] == values2Rule1[row][col]) s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor(color1);
else s.getRange(s.getRange('E1').offset(row, col, 1, 1).getA1Notation()).setBackgroundColor('white'); }}
};

I used this script generator to make creating the code easier. You can adapt it to your needs.

Google-sheets – Conditional Formatting based on another sheet’s content

Although you mention only A2 in sheet March I'm guessing you may want equivalent formatting throughout ColumnA, so start by selecting ColumnA then:

Format, Conditional formatting..., Custom formula is:

=match(A1,indirect("Roster"&"!D:D"),0)>0

select yellow (Range: should be A:A) and Save rules.

For each colour you need a separate rule (unless one colour is acceptable by default, so standard formatting applied for that) hence for red for example + Add another rule of:

=match(A1,indirect("Roster"&"!E:E"),0)>0

and repeat for blue (B:B in place of D:D in the first formula) and green (C:C).

Best Answer

Related Solutions

Google-sheets – Conditional formatting based on cell values in same column

Google-sheets – Conditional Formatting based on another sheet’s content

Related Topic