Calculation as per color of cell

[RAJESH1960]

Hello Guys,

I am trying to get the result in sheet2 from sheet 1. If the cells in sheet one is colored, then it should display the amount less by one percent or else the whole amount as shown in the image.

Query for Match and calculate.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Sheet 1		Name	Amount				Sheet 2	Name	Amount
2			January	1,000.00					January	990.00
3			February	2,000.00					February	2,000.00
4			March	3,000.00					March	3,000.00
5			April	4,000.00					April	3,960.00
6			May	1,500.00					May	1,500.00
7			June	2,500.00					June	2,500.00
8			July	3,500.00					July	3,500.00
9			August	4,500.00					August	4,455.00
10			September	5,500.00					September	5,445.00
11			October	6,500.00					October	6,500.00
12			November	7,500.00					November	7,500.00
13			December	4,500.00					December	4,500.00
14			Sunday	1,500.00					Sunday	1,485.00
15			Monday	2,500.00					Monday	2,500.00
16			Tuesday	3,500.00					Tuesday	3,500.00
17			Wednesday	4,500.00					Wednesday	4,455.00
18			Thursday	5,500.00					Thursday	5,500.00
19			Friday	6,500.00					Friday	6,500.00
20			Saturday	7,500.00					Saturday	7,500.00
Sheet1

Cell Formulas
Range	Formula
I18:J20,I2:I17,J15:J16,J11:J13,J6:J8,J3:J4	J3	=D3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:C	Expression	=MATCH(C1,$M$1:$M$9,0)	text	NO

 

[johnnyL]

@RAJESH1960 You can't check a conditional formatted cell color like that.

Replace the current line:

If 6 = Cells(i, "C").Interior.ColorIndex Then

with the following code:

If Cells(i, "C").DisplayFormat.Interior.Color = 65535 Then    ' Check for yellow fill

Try that and see if it works as you wanted.

 

[RAJESH1960]

@RAJESH1960 You can't check a conditional formatted cell color like that.

Replace the current line:

If 6 = Cells(i, "C").Interior.ColorIndex Then

with the following code:

If Cells(i, "C").DisplayFormat.Interior.Color = 65535 Then    ' Check for yellow fill

Try that and see if it works as you wanted.

Perfect. Thanks JohnnyL. This is what I needed. If it was in the form of formula it would have been much easier to prepare the project. Now have to write the complete code including your code, which is going to be a bit tough for me, as I am not so good at writing codes, for the final result. I will give it a try to write it to get the same perfect result. Thanks once again.