Forum Discussion
rhuen
Feb 26, 2019Copper Contributor
Coloring rows in Excel based on how many criteria are met
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!
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
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
- rhuenCopper Contributor
Perfect, thank you!