Custom Conditional Formatting formula

1
votes

I'd like to use Conditional Formatting in Google Sheets to color in a whole line based on certain criteria:

  • Color the line red if Column D contains a specific Value AND Column L is not empty AND Column S is empty.
  • Color the line green if Column D contains a specific value AND Column L is not empty AND Column S is also not empty
  • Do not color the line if Column L is empty, regardless of whether Column D or S have values.

So far, here's what I've tried:

=$D:$D = "specificValue"

This will color in any line in which Column D contains the specific value

=$D:$D = "specificValue" & $S:$S

This will color in any line where D contains the specific value and Column S is empty. Close, but not quite what I need.

=$D:$D = "specificValue" & $S:$S <> ""

This will Color in the whole range that I am applying the conditional formatting to.

=$D:$D = "specificValue" & $S:$S & $L:$L

This will color in any line where Column D is the value, AND BOTH Columns S and L are empty. A value in either S or L will make this line not colored. This is problematic. If D AND S have values but not L then it doesn't need to be colored. If D and L have values but not S then it needs to remain colored until I have S filled in.

1
google-sheetsgs-conditional-formatting

1 Answers

0
votes

Please select all the relevant cells in your sheet and Format, Conditional formatting..., Format cells if..., Custom formula is enter: 

=and($D1="specificvalue",not(isblank($L1)),isblank($S1))

and for Formatting style choose red fill, Done.

Then repeat with Add new rule: 

=and($D1="specificvalue",not(isblank($L1)),not(isblank($S1)))

with green fill.

The formulae are a little more verbose than really necessary but hopefully make it more obvious what is happening, as I suspect you might need to make some adjustment. Note that formatting an entire sheet may be slow. You might want to turn "specificvalue" into an absolute reference to a cell containing specificvalue.