EDIT 2: Test document. See comment of what I'm trying to accomplish in cell A1. https://docs.google.com/spreadsheets/d/1lqrPbVqSrO257PqOYKLuCTxG1iiLaL6KYawp1hu4n5c/edit#gid=0

EDIT: I've tried this code and it doesn't operate correctly. It highlights some cells, but not all within the range, and doesn't operate the duplicate identification correctly.

=if(and($I5=false), countifs($L:L, L1)>1)

Original post:

I am trying to use conditional formatting to highlight a row when the following conditions are met:

The value of cell $I1="FALSE" (i.e. the box in the cell is unchecked)


The value in $L1 has at least one duplicate value in column L.

I am currently using the following formula to only highlight the duplicate values, but cannot get it to function along with the secondary condition of $I1=FALSE.

For the range K:L (It doesn't allow me to highlight the whole row for some reason)


I also have an additional formatting rule in place that is working as I want it to:


This is highlighting the row a different color once the checkbox in column I is checked.

  • Welcome to Webapps. Please include a sample spreadsheet with what is expected. Aug 9, 2019 at 17:29

1 Answer 1


I'm unable to access your spreadsheet, but according with your report the conditional formatting formula that you're looking for is this:


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.