2

I'm trying to color a column depending on the row. My table looks like this: enter image description here

the only thing i managed to do was =NOT(ISBLANK(C101:C104)) and set it to orange using the conditional formatting.

I would like to have green if everything or everything but D:D is filled out, orange as soon as C is given and red as soon as C is missing. Any ideas how to do this? I tried:

=((NOT(ISBLANK(D42:D142)))AND(NOT(ISBLANK(F42:F142)))AND(NOT(ISBLANK(G42:G142)))AND(NOT(ISBLANK(H42:H142))))

as a conditional formatting formula for green but that didn't work.

yellow should be:

=NOT(ISBLANK(D42:D142))

and red:

=ISBLANK(D42:D142)

still I don't know which rule is applied first.

  • can you share a sample sheet - just want to test it out with some example data before i post an answer – Aurielle Perlmann Feb 26 '16 at 22:34
  • docs.google.com/spreadsheets/d/… – siryx Feb 26 '16 at 22:49
  • @AuriellePerlmann if you need more or the whole sheet, im glad to provide more. thanks for having a look at it! – siryx Feb 26 '16 at 22:54
1

FORMULAS:

RED:

=if(And(COUNTA(B42:H42)>1,isblank(D42)),true,false)

YELLOW

=if(and(istext(D42),(Or(isblank(B42),isblank(C42),isblank(E42),isblank(F42),isblank(H42)))),true,false)

GREEN

=if(counta(B42:H42)=7,true,false)
  • thanks! I wanted to color the field yellow if a value in D is given but something from B,C,F,G or H is missing. The D-Value is the most important value so as soon as it is given, the field should go from red, because not having D is problematic, to yellow, which means not finished but "Family Name" is there. Green should be finished, everything is there. – siryx Feb 27 '16 at 9:32
  • can you tell me how to adjust this to a different range? on the original spreadsheet, I wanted to color the numbers, so the A column. The table starts at A42 and goes to H542. – siryx Feb 27 '16 at 9:39
  • I put the table at the same position it is it the original spreadsheet, still copy-pasting the formula from the testsheet, which is working there, even in the new range, to the exact same range in the original sheet isnt working. – siryx Feb 27 '16 at 9:54
  • Check now - I fixed it for you – Aurielle Perlmann Feb 27 '16 at 21:21
  • I edited my answer to show the corrections – Aurielle Perlmann Feb 27 '16 at 21:26

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.