Highlight Based on Cell Reference Location

Copper Contributor

I have a work book that I have multiple pricing sheets in that we have been able to go through entire jobs to find the best purchase. The sheets are based on Master sheets in another work book so they are formulas that create a value. I am currently running a min formula on a new sheet and having it pull the lowest value for specific Items to try to compare numbers from each sheet for a single product. I am hoping there is a way to create a highlight or some way to color the cell based on which sheet is pulls the cells being referenced from. 

Ex. I Have Bread coming from 10 distributors. I have some distributors that are offering multiple manufactures so they have multiple cells on their sheet. I created a targeted Min formula to reference each cell in each worksheet that contained a price for bread. It gives me the best number from across the cells. If I could highlight that cell based on the location of that value it would be easier to track the data across multiple locations and because the data that I am referencing is being pulled from another workbook I am not sure how to create the proper VBA or Condition to give me a marker for ea

2 Replies
which sheet is pulls the cells being referenced from

How about consolidate all sheets to one sheet before furture analysis?
The Master work book is pulling data from 9 other workbooks I am trying to create a sheet inside the Master work book to view the max and min prices for each product. This sheet will have around 500 Rows to accomplish this. My hope is to have a way to highlight or color code the max and min result based on the location of the referenced cell it pulls it data. The issue with trying to consolidate the data is the product descriptions vary for the same product reference so I am creating targeted cells for the max and min