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

 

[RAJESH1960]

The figures in Column J are entered manually and the cells too are colored manually just to show what result I am trying to get and in which cells. If I change January to February in cell C2, then automatically he amount in column J2 should display 1000.

 

[kweaver]

How about this?

Sub Rajesh()
Dim lr As Long, i As Long, ct As Long
lr = Cells(Rows.Count, "C").End(xlUp).Row
For i = 2 To lr
 If 6 = Cells(i, "C").Interior.ColorIndex Then
 Cells(i, "J") = 0.9 * Cells(i, "D").Value
 Cells(i, "J").Interior.ColorIndex = 6
 Else
 Cells(i, "J") = Cells(i, "D").Value
 End If
Next i
End Sub

 

[johnnyL]

@kweaver :

Sub Rajesh()
'
    Dim lr As Long, i As Long, ct As Long
'
    lr = Cells(Rows.Count, "C").End(xlUp).Row
'
    For i = 2 To lr
        If 6 = Cells(i, "C").Interior.ColorIndex Then       ' Is it 6 or is it 27 ? Difficult to distinguish sometimes.
            Cells(i, "J") = 0.9 * Cells(i, "D").Value
            Cells(i, "J").Interior.ColorIndex = 6           ' Is it 6 or is it 27 ? Difficult to distinguish sometimes.
        Else
            Cells(i, "J") = Cells(i, "D").Value
        End If
    Next i
End Sub

 

[kweaver]

I usually check it w/the ColorIndex.

 

[johnnyL]

I usually check it w/the ColorIndex.

That requires a function right?

 

[RAJESH1960]

In both the codes I am getting 1000 in cell j2 which is supposed to show 990 as January is formatted by condiftional formatting.

 

[RAJESH1960]

@kweaver :

Sub Rajesh()
'
    Dim lr As Long, i As Long, ct As Long
'
    lr = Cells(Rows.Count, "C").End(xlUp).Row
'
    For i = 2 To lr
        If 6 = Cells(i, "C").Interior.ColorIndex Then       ' Is it 6 or is it 27 ? Difficult to distinguish sometimes.
            Cells(i, "J") = 0.9 * Cells(i, "D").Value
            Cells(i, "J").Interior.ColorIndex = 6           ' Is it 6 or is it 27 ? Difficult to distinguish sometimes.
        Else
            Cells(i, "J") = Cells(i, "D").Value
        End If
    Next i
End Sub

Please try and Use a single option in the code for non color and color cells. Its either white cell or conditional formatted with any other color cell.

 

[kweaver]

In both the codes I am getting 1000 in cell j2 which is supposed to show 990 as January is formatted by condiftional formatting.

As you can see from my sample a few posts up, I use

Cells(i, "J") = 0.9 * Cells(i, "D").Value

which takes 10% off (which is 90% on).

 

[RAJESH1960]

Please try and Use a single option in the code for non color and color cells. Its either white cell or conditional formatted with any other color cell.

It’s conditional formatting with one single colour