I'm trying to color a column depending on the row. My table looks like this: 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
• @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

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