Conditional format groups of rows based on multiple criteria

[wiseone]

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!

 

[Eric W]

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).

 

[wiseone]

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.