Conditional Formatting by Cell Value (Not at Row or Column Level)

[BPurcell]

I'm looking to add conditional formatting into a job I'm building.  

Here's a simplistic example (workflow attached)

I'd like to color code based off product status which could vary by row/column.  

How can I build the formatting?  Is there an easy way if the rows & columns get much bigger -- (i.e. 100 x 100)?  (i.e. not building rules for each column/row)

[Jonathan-Sherman]

Hi @BPurcell,

If you're going to add more and more columns I would probably advise changing the table structure to have a new row for each location and product scenario and therefore one other column for status (3 columns in all), rather than your existing table which has a column for each product at a certain location which could get unwieldy very quickly. This would also make it much easier to manage your row level formatting.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workbook for you to download if needed.

Regards,

Jonathan

[BPurcell]

Thanks for the suggestion, but specific cell formatting is a requirement.  You did give me an idea transpose the data as you suggested, build a batch macro on each column, and append the row header.  Then append everything together using layout tools.  

I'm not sure how robust it would be, but I did work through a bit of this.  I tried out changing the formatting based on the cell value.  I'm not an app expert, but it seems like you would have to create a bunch of dummy conditional formatting then use a text box, and the desired formatting (in my case background color).

Not the most elegant solution, but one that could be used in a pinch.  

Thanks for your help.