• 547K Members
• 3,155 Online
• 652K Conversations
SOLVED

## Coloring rows in Excel based on how many criteria are met

Highlighted
Occasional 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!

2 Replies
Highlighted
Solution

# Re: Coloring rows in Excel based on how many criteria are met

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

Highlighted

# Re: Coloring rows in Excel based on how many criteria are met

Perfect, thank you!

Related Conversations
Creating Pie charts.......
dougler2020 in Excel on
0 Replies
How to use the trendline to find percentage change?
fishfish31 in Excel on
0 Replies
tableau
Noemie911 in Excel on
4 Replies
Summarzing data
Ravi_Kumar in Excel on
1 Replies