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.

Improve this question
3
  • 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, 2016 at 22:34
  • docs.google.com/spreadsheets/d/…
    – siryx
    Feb 26, 2016 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, 2016 at 22:54

1 Answer 1

Reset to default
3

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)
Improve this answer
5
  • 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, 2016 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, 2016 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, 2016 at 9:54
  • Check now - I fixed it for you
    – Aurielle Perlmann
    Feb 27, 2016 at 21:21
  • I edited my answer to show the corrections
    – Aurielle Perlmann
    Feb 27, 2016 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.