Conditional format groups of rows based on multiple criteria

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
136
I have some data as listed in the below table with an example of how it would conditionally format.

It should format the rows based on the following criteria:
  1. The group of items is highlighted based on column B* IF:
    • The first row of the group in cell column C is not blank.
    • The data in columns D and E for the group are not blank.
  2. There is a blank row between groups, this should not highlighted.
*I would prefer it to group just based on Columns C D and E, and recognized they are separated by blank rows, but I don't think this is possible...so I instead suggested column B as a means to group them....if you think you can suggest a way to ignore column B....I'd love to see your suggestion.

Thanks in advance for your help!

1619104785848.png
 

Attachments

  • 1619104382061.png
    1619104382061.png
    8.9 KB · Views: 1

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,979
Are you specifically having performance issues? The COUNTIF and COUNTIFS functions are "aware" of the last row used, so if you only have 500 lines, that's all it looks at. If you've done something odd, like having a few thousand lines at the end, then deleted those lines, then Excel might not know the "real" end until you save the workbook again. So that leaves the MATCH function. I did not specifically test the formula on a large range, but I thought that since the MATCH would definitely find a match (except for blank rows) within the actual range, it should go quickly. But if you are having performance issues, or you just want to switch to a specific range, you can do it. You're very close in fact:

=AND(INDEX($AI$6:$AI$500,MATCH($R6,$R$6:$R$500,0))<>"",COUNTIF($R$6:$R$500,$R6)*3=COUNTIFS($R$6:$R$500,$R6,$AC$6:$AC$500,"<>")+COUNTIFS($R$6:$R$500,$R6,$AD$6:$AD$500,"<>")+ COUNTIFS($R$6:$R$500,$R6,$AK$6:$AK$500,"<>"))

You just needed to make the ranges absolute. I also realized that the $A6<>"" condition wasn't necessary (at least on my sample sheet).
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
136
Thanks, I'll test it out again, delete anything that might be hidden and save and see if it works without performance problems. Appreciate it.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,614
Messages
5,659,846
Members
418,532
Latest member
roynaz11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top