Highlight bottom cells if above cells criteria meets

[jueqin]

Hi,

I need some advise, I have a series of data representing warehouse location, i only want to highlight the rest of the warehouse when there is stock in warehouse north and after total, restart the criteria.

If warehouse north has no stock then no action would be taken.

Not sure what formula I need to apply. I can't use VBA and macro due to company policies.

Thanks

excel1.JPG

[etaf]

not sure i follow the example
you have total all highlighted on the rows - even if north is zero or not

so if North has more than zero - you want all the warehouses highlighted
does every group after total have the exact same number ?
as row 23-25 is different

as row 9 north has 3
then you want to highlight row 10,11,12,13 and 14 - what about the total row 15? and what about the north row 9 ? does that also highlight

[jueqin]

Hi Etaf,

Total is an indication of the end of the particular line item

I have many line item, some appear in all warehouse, some only appear in North and 1 or 2 other warehouse.

What I need is for that particular line item, if North warehouse has stock, the remaining warehouse are been highlighted.

I have tried to use offset function in conditional formatting but failed.

Pls do let me know if my question is still unclear

Sorry for the inconvenience caused.

Thanks for replying

[etaf]

I'm not sure how to do that, i'm afraid

[samba_ravi]

Attach sample file with highlight the cells as per your requirement manually

[jueqin]

Dear all,

Thanks for the efforts, I had resolved my issues.

I used AND with OFFSET in conditional formatting.

Thank you

[etaf]

would be interested to see that - so i learn a bit - i was not able to get it to work when playing yesterday

[sktneer]

Try this to see if this is what you are trying to achieve.
Select the range A2:B26 and make a New Rule for conditional formatting and set the format as per your choice.
For details see the attached where I have applied the conditional formatting to add yellow color and the cell borders to the cells which meet the criteria.

Please Login or Register  to view this content.