I am trying to combine formulas here, to put color on specific cells (out of a specific range) when they are not empty, and when a checkbox in another cell is checked.

Here is what I tried :


D4 is where the checkbox is, and if it is checked, the color must apply on the cells from F4 to AU only is they are not empty! As you can see on the picture below, D column is ticked so the whole range is colored, but I need only where is is written "2" in that line.

enter image description here

Can someone help me?

I tried the IF formula also, but could not get through. Same with AND and using TRUE, or ISBLANK for F4:AU.

  • You select range to which Conditional formatting will be applied in "Apply to range" section. No need to dub it in formula, try =AND($D$4=TRUE,F4<>””)
    – Oleg_S
    Commented Oct 28, 2020 at 13:24
  • Thank you for taking time to address my question ! I tried it, although nothing came out of it. I put the whole range in the formula, as each cell from F to AU that is not empty, when the D cell of that line is ticked (checkbox), I need to apply the color format to it.
    – Lisa
    Commented Oct 28, 2020 at 15:05
  • I have updated the question with a screenshot and more explanations. Thank you !! ^^
    – Lisa
    Commented Oct 28, 2020 at 15:14
  • ok i see, since you have tickbox for each row there is no need for absolute ref $D$4 from my previous comment. Try =AND($D4=TRUE,F4<>””)
    – Oleg_S
    Commented Oct 28, 2020 at 15:22
  • Formula is staying red, and will not apply. I also tried =AND($D4=TRUE,$F4:AU<>"") to make sure it applied to the whole range if cell is not empty, but still nothing.
    – Lisa
    Commented Oct 28, 2020 at 15:28


Your Answer

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