Coloring rows in Excel based on how many criteria are met

[rhuen]

I have just gotten into working with the developer tab of Excel, and I need some help. I am trying to find an easier way to color rows on a sheet based on the number of criteria that is met. I could technically do this with Conditional Formatting. I actually started to do it before deciding how ridiculous it would be:

ALL

=AND(OR($D2>55, $G2<=1979),$E2="Male",OR($F2="Single",$F2="Widowed"),$L2>5,$M2>100)

MISSING ONE

=OR(AND(OR($D2>55, $G2<=1979),$E2="Male",OR($F2="Single",$F2="Widowed"),$L2>5), AND(OR($D2>55, $G2<=1979),$E2="Male",OR($F2="Single",$F2="Widowed"),$M2>100), AND(OR($D2>55, $G2<=1979),$E2="Male",$L2>5,$M2>100), AND(OR($D2>55, $G2<=1979), OR($F2="Single",$F2="Widowed"),$L2>5,$M2>100), AND($E2="Male",OR($F2="Single",$F2="Widowed"),$L2>5,$M2>100))

Don't bother looking into that too much. I'll explain the criteria: The perfect constituent is older than 55 OR graduated before 1979, is male, is widowed OR single, has five gifts or more, and has given $100 or more. If they meet the maximum of five of these, the row is highlighted orange. If they meet four, it's blue And so on~

I will attach an example workbook of what the final product should look like and a plain worksheet I am trying to apply it to. Please ask questions if you have any. Thank you in advance!

[#2 · Accepted solution]

Hi, 

that could be like

=((OR(INDIRECT("Table1[@AGE]")>55,INDIRECT("Table1[@CLASS]")<1979))+(INDIRECT("Table1[@GENDER]")="Male")+(OR(INDIRECT("Table1[@STATUS]")="Widowed",INDIRECT("Table1[@STATUS]")="Single"))+(INDIRECT("Table1[@['# OF GIFTS]]")>=5)+(INDIRECT("Table1[@[TOTAL GIFT AMOUNT]]")>=100))=5

for first rule, etc

[#3]

Perfect, thank you!