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

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,979
This basically counts the number of blank rows in column A:

Book2
ABCDE
1Header 1Header 2 Header 3 Header 4 Header 5
2AAA21-000$ 100.00$ 123.00$ 2,342.00
3BBB21-000$ 456.00$ 23,423.00
4CCC21-000$ 789.00$ 3,242.00
5
6DDD21-001$ 15.00$ 5,463.00$ 564.00
7EEE21-001$ 3,563.00
8EEE21-001$ 564.00$ 7,657.00
9
10FFF21-002$ 456.00$ 675.00$ 968.00
11FFF21-002$ 909.00$ 69.00
12FFF21-002$ 6,574.00$ 7,675.00
13
14GGG21-003$ 5,463.00$ 564.00
15HHH21-003$ 89.00$ 3,563.00
16HHH21-003$ 564.00$ 7,657.00
17
18JJJ21-004
19JJJ21-004
20KKK21-004$ 123.00
21KKK21-004
22
23LLL21-005$ 456.00
24MMM21-005$ 789.00
Sheet14
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:E24Expression=($A2<>"")*(MOD(SUMPRODUCT(--($A$1:$A1="")),2)=0)textNO
 

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
136
=($A2<>"")*(MOD(SUMPRODUCT(--($A$1:$A1="")),2)=0)
Hi Eric, I'm not sure what this is supposed to do, but all its doing in my spreadsheet is hilighting what appears to be random rows. It doesn't appear there are conditions which apply to columns B,C,D or E. Am I supposed to append this formula with some more information?
 

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
136
Hi Eric, I'm not sure what this is supposed to do, but all its doing in my spreadsheet is hilighting what appears to be random rows. It doesn't appear there are conditions which apply to columns B,C,D or E. Am I supposed to append this formula with some more information?
Missed the edit time.... I played with it abit more and I see what happened..... Here was my edited message.

Hi Eric, I'm not sure what this is supposed to do, but all its doing in my spreadsheet is highlighting what appears to be every other group. It doesn't appear there are conditions which apply to columns B,C,D or E. Am I supposed to append this formula with some more information?

In my sample, the groups 21-001 and 21-003 are missing information in cells D7 and C14, that is why they are not hilighted.
 

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
136

ADVERTISEMENT

Bump....anyone able to help with this one?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,979
Sorry, I've been away for a while, and it looks like I misread your requirements. How about:

Book2
ABCDE
1Header 1Header 2 Header 3 Header 4 Header 5
2AAA21-000$ 100.00$ 123.00$ 2,342.00
3BBB21-000$ 456.00$ 23,423.00
4CCC21-000$ 789.00$ 3,242.00
5
6DDD21-001$ 15.00$ 5,463.00$ 564.00
7EEE21-001$ 3,563.00
8EEE21-001$ 564.00$ 7,657.00
9
10FFF21-002$ 456.00$ 675.00$ 968.00
11FFF21-002$ 909.00$ 69.00
12FFF21-002$ 6,574.00$ 7,675.00
13
14GGG21-003$ 5,463.00$ 564.00
15HHH21-003$ 89.00$ 3,563.00
16HHH21-003$ 564.00$ 7,657.00
17
18JJJ21-004
19JJJ21-004
20KKK21-004$ 123.00
21KKK21-004
22
23LLL21-005$ 456.00
24MMM21-005$ 789.00
25
26
27
28
29
30
31
Sheet14
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:E31Expression=AND($A2<>"",INDEX($C:$C,MATCH($B2,$B:$B,0))<>"",COUNTIF($B:$B,$B2)*2=COUNTIFS($B:$B,$B2,$D:$D,"<>")+COUNTIFS($B:$B,$B2,$E:$E,"<>"))textNO
 
Solution

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
136

ADVERTISEMENT

Sorry, I've been away for a while, and it looks like I misread your requirements. How about:

Book2
ABCDE
1Header 1Header 2 Header 3 Header 4 Header 5
2AAA21-000$ 100.00$ 123.00$ 2,342.00
3BBB21-000$ 456.00$ 23,423.00
4CCC21-000$ 789.00$ 3,242.00
5
6DDD21-001$ 15.00$ 5,463.00$ 564.00
7EEE21-001$ 3,563.00
8EEE21-001$ 564.00$ 7,657.00
9
10FFF21-002$ 456.00$ 675.00$ 968.00
11FFF21-002$ 909.00$ 69.00
12FFF21-002$ 6,574.00$ 7,675.00
13
14GGG21-003$ 5,463.00$ 564.00
15HHH21-003$ 89.00$ 3,563.00
16HHH21-003$ 564.00$ 7,657.00
17
18JJJ21-004
19JJJ21-004
20KKK21-004$ 123.00
21KKK21-004
22
23LLL21-005$ 456.00
24MMM21-005$ 789.00
25
26
27
28
29
30
31
Sheet14
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:E31Expression=AND($A2<>"",INDEX($C:$C,MATCH($B2,$B:$B,0))<>"",COUNTIF($B:$B,$B2)*2=COUNTIFS($B:$B,$B2,$D:$D,"<>")+COUNTIFS($B:$B,$B2,$E:$E,"<>"))textNO
Works like a charm. Thank you so much!
 

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
136
One more question regarding this. This formula is demanding as its checking all rows in the spreadsheet. I only need it to work for about 500 rows. However, I often insert rows, or delete rows or move rows around.

Is there a way to make the conditional format stay "clean" so it doesn't duplicate and adjust as I cut/paste/insert/delete rows?

Here is the final formula I tried based on your solution (which doesn't work unfortunately)...only hilighting 1st row). It works if I don't specify rows)

Code:
=AND($A6<>"",INDEX($AI6:$AI500,MATCH($R6,$R6:$R500,0))<>"",COUNTIF($R6:$R500,$R6)*3=COUNTIFS($R6:$R500,$R6,$AC6:$AC500,"<>")+COUNTIFS($R6:$R500,$R6,$AD6:$AD500,"<>")+ COUNTIFS($R6:$R500,$R6,$Ak6:$Ak500,"<>"))
 

Watch MrExcel Video

Forum statistics

Threads
1,133,616
Messages
5,659,866
Members
418,535
Latest member
Ajith55

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