Search code examples
google-sheetsgoogle-sheets-formulags-conditional-formatting

Format cells in column based on first cell in Google Sheets

I'd like to create a table with checkboxes:

  • one column for one day, date in the first cell
  • if checkbox is true = fill green
  • if checkbox is false and date in the first cell is today or earlier = fill cell red

I've found how to do that for specific column, e.g. for F column:

  • Green: Text is exactly FALSE
  • Red: Custom formula is =(TODAY()-F1>0)+FALSE

But when I try to create conditional formatting for multiple columns - it is still based on F1 value

How to make it work for all columns based on the first cell of the same column

Solution

  • green color custom formula:

    =F$2=TRUE

    red color custom formula:

    =(F$2=FALSE)*(F$1<=TODAY())

    0

    EXAMPLE 2:

    green color custom formula:

    =A2=TRUE

    red color custom formula

    =(A2=FALSE)*(A$1<=TODAY())

    0