Conditional Highlight

Colin Hayes · Dec 2, 2013

Hi All

In column B I have a list of products :

Product A
Product B
Product C
Product D
Product E

and so on.

In column C there are various conditions listed one by one against each
product :

In Stock
Out of Stock
Pending

What I'd like to do is to highlight a Product once all of the three
conditions is detected.

For example ,

B C

Product A In Stock
Product A Pending

Would produce no highlight , but

B C

Product A In Stock
Product A Pending
Product A Out Of Stock

Would make the all the relevant text highlight to show that the three
conditions were present for Product A.

Can someone help?

Grateful for any help.

Are you aware that we also buy CDs, Vinyl and DVDs? Send your lists of
unwanted items to (e-mail address removed) and weâ€™ll quote you a priceâ€¦

You can browse and buy direct from my full list of items at these addresses :

http://s1.amazon.co.uk/exec/varzea/ts/my-zshop/SPJK3X6KOJZR6/026-3393902-9
050050

or:

http://www.CDandVinyl.co.uk

or :

http://www.netsoundsmusic.com/cdandvinyl

or:

http://chayes.musicstack.com

To DOWNLOAD the full catalogue click here :

http://www.chayes.demon.co.uk/CDandVinyl_full_catalogue.exe

Best Wishes ,

Colin Hayes.

TEL / FAX : (UK) (0)208 804 9181

Claus Busch · Dec 3, 2013

Hi Colin,

Am Mon, 2 Dec 2013 21:36:56 +0000 schrieb Colin Hayes:

Product A In Stock
Product A Pending
Product A Out Of Stock

Would make the all the relevant text highlight to show that the three
conditions were present for Product A.

try this formula in Conditional Formatting:
=SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$100,0)=ROW($1:$100)*($B$1:$B$100=B1)*($C$1:$C$100<>""))*1)=3

Regards
Claus B.

Colin Hayes · Dec 3, 2013

Claus Busch said:
Hi Colin,

Am Mon, 2 Dec 2013 21:36:56 +0000 schrieb Colin Hayes:

try this formula in Conditional Formatting:
=SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$1
00,0)=ROW($1:$100)*($B$1:$B$100=B1)*($C$1:$C$100<>""))*1)=3

Hi Claus

OK Thanks again for this. It works perfectly.

Although I'm applying it to the whole sheet it highlights entries in
only one column rather then the whole row , but this is fine.

Best Wishes

Claus Busch · Dec 3, 2013

Hi Colin,

Am Tue, 3 Dec 2013 14:37:53 +0000 schrieb Colin Hayes:

Although I'm applying it to the whole sheet it highlights entries in
only one column rather then the whole row , but this is fine.

then you have to write the column of B1 absolute:
=SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$100,0)=ROW($1:$100)*($B$1:$B$100=$B1)*($C$1:$C$100<>""))*1)=3

Regards
Claus B.

Colin Hayes · Dec 3, 2013

Claus Busch said:
Hi Colin,

Am Tue, 3 Dec 2013 14:37:53 +0000 schrieb Colin Hayes:

then you have to write the column of B1 absolute:
=SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$1
00,0)=ROW($1:$
100)*($B$1:$B$100=$B1)*($C$1:$C$100<>""))*1)=3

Regards
Claus B.

Hi Claus

Brilliant - thanks.

Best Wishes

Colin Hayes · Dec 3, 2013

Claus Busch said:
Hi Colin,

Am Tue, 3 Dec 2013 14:37:53 +0000 schrieb Colin Hayes:

then you have to write the column of B1 absolute:
=SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$1
00,0)=ROW($1:$100)*($B$1:$B$100=$B1)*($C$1:$C$100<>""))*1)=3

Regards
Claus B.

Hi Claus

An extra thing I thought I could add myself but have failed :

I have a Quantity column E. I need the formatting for the rows to revert
to 'Automatic' when the quantity is 0. I thought I could add an extra
Condition to cover this , but it doesn't seem to work.

Effectively it would ask Excel to format the relevant rows if the
formula above AND the relevant quantity is above 0. Rows below 1 would
not count as part of the 3 required for the formatting.

Can this be done?

Thanks for your patience.

Best Wishes

Claus Busch · Dec 3, 2013

Hi Colin,

Am Tue, 3 Dec 2013 21:00:25 +0000 schrieb Colin Hayes:

I have a Quantity column E. I need the formatting for the rows to revert
to 'Automatic' when the quantity is 0. I thought I could add an extra
Condition to cover this , but it doesn't seem to work.

try:
=AND($E1<>0,SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$100,0)=ROW($1:$100)*($B$1:$B$100=$B1)*($C$1:$C$100<>""))*1)=3)

Regards
Claus B.

Colin Hayes · Dec 3, 2013

Claus Busch said:
try:
=AND($E1<>0,SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$10
0&$C$1:$C$100,
0)=ROW($1:$100)*($B$1:$B$100=$B1)*($C$1:$C$100<>""))*1)=3)

Regards
Claus B.

Hi Claus

Thanks for this. It worked well on the line at zero of course , but
leaves in place the other formatting. It's so complicated.

I have chosen red for my formatting. When the three criteria are met ,
and the cell in E is greater than zero , the three lines go red.
Correctly.

When I make one of the quantities 0 , then that line goes to automatic ,
and the other 2 stay red. Curious.

Ideally they would all go to automatic when one of the three no longer
meets the criteria , and all go red again when all three are above zero
in E.

This is a bigger mountain than I thought!

Best Wishes

Claus Busch · Dec 4, 2013

Hi Colin,

Am Tue, 3 Dec 2013 22:13:48 +0000 schrieb Colin Hayes:

When I make one of the quantities 0 , then that line goes to automatic ,
and the other 2 stay red. Curious.

sorry, I misunderstood your problem.
Try:
=IF(COUNTIFS($B$1:$B$100,$B1,$E$1:$E$100,0)>0,0,SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$100,0)=ROW($1:$100)*($B$1:$B$100=$B1)*($C$1:$C$100<>""))*1))=3

Regards
Claus B.

Claus Busch · Dec 4, 2013

Hi again,

Am Wed, 4 Dec 2013 10:56:56 +0100 schrieb Claus Busch:

=IF(COUNTIFS($B$1:$B$100,$B1,$E$1:$E$100,0)>0,0,SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$100,0)=ROW($1:$100)*($B$1:$B$100=$B1)*($C$1:$C$100<>""))*1))=3

or:
=(COUNTIFS($B$1:$B$100,$B1,$E$1:$E$100,0)=0)*(SUMPRODUCT((MATCH($B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$100,0)=ROW($1:$100)*($B$1:$B$100=$B1)*($C$1:$C$100<>""))*1)=3)

Regards
Claus B.

Colin Hayes · Dec 4, 2013

Claus Busch said:
Hi Colin,

Am Tue, 3 Dec 2013 22:13:48 +0000 schrieb Colin Hayes:

sorry, I misunderstood your problem.
Try:
=IF(COUNTIFS($B$1:$B$100,$B1,$E$1:$E$100,0)>0,0,SUMPRODUCT((MATCH($
B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$100,0)=ROW($1:$100)*($B$1:
$B$100=$B1)*($C$1:$C$100<>""))*1))=3

Regards
Claus B.

Hi Claus

Sorry if I didn't explain well.

This combination would respond to conditional formatting : (I colour
red)

C D E

Product A In Stock 1
Product A Out Of Stock 4
Product A Pending 3

Three qualities for a single product , all quantities greater than 0.
All red.

This combination would *not* respond to conditional formatting

C D E

Product A In Stock 0
Product A Out Of Stock 4
Product A Pending 3

Three qualities for a single product , but all quantities not greater
than 0. None red

I hope it's clearer. Sorry for being tiresome.

Best Wishes

Colin Hayes · Dec 4, 2013

Claus Busch said:
Hi Colin,

Am Tue, 3 Dec 2013 22:13:48 +0000 schrieb Colin Hayes:

sorry, I misunderstood your problem.
Try:
=IF(COUNTIFS($B$1:$B$100,$B1,$E$1:$E$100,0)>0,0,SUMPRODUCT((MATCH(
$
B$1:$B$100&$C$1:$C$100,$B$1:$B$100&$C$1:$C$100,0)=ROW($1:$100)*($B$
1:
$B$100=$B1)*($C$1:$C$100<>""))*1))=3

Regards
Claus B.

Hi Claus

Sorry if I didn't explain well.

This combination would respond to conditional formatting : (I colour
red)

C D E

Product A In Stock 1
Product A Out Of Stock 4
Product A Pending 3

Three qualities for a single product , all quantities greater than 0.
All red.

This combination would *not* respond to conditional formatting

C D E

Product A In Stock 0
Product A Out Of Stock 4
Product A Pending 3

Three qualities for a single product , but all quantities not greater
than 0. None red

I hope it's clearer. Sorry for being tiresome. I already changed
reference to B and C in your formula to match C and D with no joy.

Best Wishes

Claus Busch · Dec 4, 2013

Hi Colin,

Am Wed, 4 Dec 2013 14:14:15 +0000 schrieb Colin Hayes:

This combination would respond to conditional formatting : (I colour
red)

C D E

Product A In Stock 1
Product A Out Of Stock 4
Product A Pending 3

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "CF_Colin"

Regards
Claus B.

Colin Hayes · Dec 4, 2013

Claus Busch said:
Hi Colin,

Am Wed, 4 Dec 2013 14:14:15 +0000 schrieb Colin Hayes:

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!3
26
for the workbook "CF_Colin"

Regards
Claus B.

Hi Claus

OK that's brilliant. Problem solved.

Thanks again for your time and considerable expertise. I'm impressed!

Best Wishes

Colin

Colin Hayes · Jan 9, 2014

Hi Claus

Thanks very much for this. I'm trying to adapt the conditional
formatting to count horizontally , rather than vertically.

I've added a 'Horizontal' sheet to the workbook , but I'm having trouble
adapting the formatting formula to read across rather than down. Can you
help?

The only difference would be that the quantity needed changed from 3 to
whatever is shown in H.

Thanks Claus I'm grateful.

The file is here :

http://www.chayes.demon.co.uk/CF_Colin_Horizontal_Working.xls

Claus Busch · Jan 10, 2014

Hi Colin,

Am Thu, 9 Jan 2014 23:54:11 +0000 schrieb Colin Hayes:

Thanks very much for this. I'm trying to adapt the conditional
formatting to count horizontally , rather than vertically.

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for workbook "CF_Horizontal"

Regards
Claus B.

Colin Hayes · Jan 10, 2014

Claus Busch said:
Hi Colin,

Am Thu, 9 Jan 2014 23:54:11 +0000 schrieb Colin Hayes:

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!3
26
for workbook "CF_Horizontal"

Regards
Claus B.

Hi Claus

Perfect solution. Many thanks Claus for your time and expertise.

Best Wishes

Colin