Multiplication Based on Cell Color

[CodingMonkey]

Hey all! Hoping someone here is a bit smarter as I've ran into a mental roadblock.

My Worksheet:
I have a inventory worksheet with 4 columns (ID, Product Name, QTY, Total Price) and 788 rows. We are offered pricing based on the "QTY" column, for example 1-10 is $55.00, 11-50 is $25.00. I will provide a mock set of data at end of post for further understanding.

My Situation:
Originally I thought I could just do a simple "=IF(and(" logical statement, but with the amount of pricing points that are offered I reached the limit of logical statements and Excel error'd out. My next progression was to use conditional formatting in the "QTY" column to color cells if they have a value between X and X.

My Question:
Is there a way to make Excel look at the "QTY" column, see the color, then multiply it by an assigned value ("Price" column in Pricing Ranges table) to give me "Total Price"?

Is there a better way to manage this situation better then what I have currently come up with? I'm more then open to ideas!

Sample Data:

ID	Product Name	QTY	Total Price
100000-001	Product 1	113
100000-002	Product 2	243
100000-003	Product 3	5
100000-004	Product 4	1
100000-005	Product 5	477
100000-006	Product 6	234
100000-007	Product 7	23

Pricing Ranges

Min QTY	Max QTY	Price
1	10	$55.00
11	50	$25.00
51	100	$15.00
101	250	$10.00
251	500	$8.00
500		$5.00

 

[Fluff]

How about

+Fluff.xlsm
	A	B	C
1	Min QTY	Max QTY	Price
2	1	10	55
3	11	50	25
4	51	100	15
5	101	250	10
6	251	500	8
7	501		5
Sheet2

+Fluff.xlsm
	A	B	C	D
1	ID	Product Name	QTY	Total Price
2	100000-001	Product 1	113	1130
3	100000-002	Product 2	243	2430
4	100000-003	Product 3	5	275
5	100000-004	Product 4	1	55
6	100000-005	Product 5	477	3816
7	100000-006	Product 6	234	2340
8	100000-007	Product 7	23	575
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=C2*VLOOKUP(C2,Sheet2!$A$2:$C$7,3,1)

 

[CodingMonkey]

How about

+Fluff.xlsm
	A	B	C
1	Min QTY	Max QTY	Price
2	1	10	55
3	11	50	25
4	51	100	15
5	101	250	10
6	251	500	8
7	501		5
Sheet2

+Fluff.xlsm
	A	B	C	D
1	ID	Product Name	QTY	Total Price
2	100000-001	Product 1	113	1130
3	100000-002	Product 2	243	2430
4	100000-003	Product 3	5	275
5	100000-004	Product 4	1	55
6	100000-005	Product 5	477	3816
7	100000-006	Product 6	234	2340
8	100000-007	Product 7	23	575
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=C2*VLOOKUP(C2,Sheet2!$A$2:$C$7,3,1)

Fluff,

You're a saint! Simple as that!

I really need to remember VLOOKUP more often.

Thank you so much for your help and such a quick response at that!

*bow head*

 

[Fluff]

You're welcome & thanks for the feedback