Conditional format groups of rows based on multiple criteria

wiseone

Board Regular
Joined
Mar 14, 2015
Messages
145
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: 8

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
=($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?
 
Upvote 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?
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.
 
Upvote 0
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
 
Upvote 0
Solution
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!
 
Upvote 0
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,"<>"))
 
Upvote 0

Forum statistics

Threads
1,224,829
Messages
6,181,218
Members
453,024
Latest member
Wingit77

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
Back
Top