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
    Oct 28 '20 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
    Oct 28 '20 at 15:05
  • I have updated the question with a screenshot and more explanations. Thank you !! ^^
    – Lisa
    Oct 28 '20 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
    Oct 28 '20 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
    Oct 28 '20 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.