Color range on some condition with VBA

[kashif.special2005]

Hi,

I have data, and I want to color cell on the column B ("Deposit") and column C ("Withdrawal"), on some condition.

Condition:-

Rule 1 :- Deposit & withdrawal entry for same date and same amount - color code "green"
Rule 2 :- Deposit & withdrawal entry for different dates with same amount - color code "yellow"
Rule 3 :- One Deposit but 2 withdrawals of same amount on same date then color code as "red"
Rule 4 :- Same date with different amounts of deposit and withdrawal then color code "red"
Rule 5 :- Only one entry either depoist or withdrawal then - color code "no color"

Data Heading:-

A1=Date
B1=Deposit
C1=Withdrawal

Data:-

A2_____02/07/2018___B2_____8605.59___C2_IS BLANK
A3_____02/07/2018___B3_IS BLANK___C3_____8606
A4_____02/08/2018___B4_IS BLANK___C4_____5086
A5_____02/08/2018___B5_____82320___C5_IS BLANK
A6_____02/08/2018___B6_IS BLANK___C6_____93534
A7_____02/08/2018___B7_____11214___C7_IS BLANK
A8_____02/14/2018___B8_IS BLANK___C8_____99.93
A9_____02/15/2018___B9_IS BLANK___C9_____29096
A10_____02/15/2018___B10_____29096___C10_IS BLANK
A11_____02/16/2018___B11_____1000000___C11_IS BLANK
A12_____02/16/2018___B12_IS BLANK___C12_____1000000
A13_____02/20/2018___B13_IS BLANK___C13_____1000000
A14_____02/20/2018___B14_IS BLANK___C14_____750
A15_____02/20/2018___B15_____1000000___C15_IS BLANK
A16_____02/20/2018___B16_____750___C16_IS BLANK
A17_____02/21/2018___B17_IS BLANK___C17_____68720
A18_____02/21/2018___B18_____75000___C18_IS BLANK
A19_____02/21/2018___B19_IS BLANK___C19_____6280
A20_____02/22/2018___B20_____200000___C20_IS BLANK
A21_____02/22/2018___B21_IS BLANK___C21_____200000
A22_____02/26/2018___B22_____1428193.84___C22_IS BLANK
A23_____02/26/2018___B23_IS BLANK___C23_____1428193.84
A24_____02/27/2018___B24_____1500000___C24_IS BLANK
A25_____02/27/2018___B25_IS BLANK___C25_____1500000
A26_____02/28/2018___B26_IS BLANK___C26_____4809.77
A27_____02/28/2018___B27_____500000___C27_IS BLANK
A28_____02/28/2018___B28_____3265431.51___C28_IS BLANK
A29_____02/28/2018___B29_IS BLANK___C29_____1500000
A30_____02/28/2018___B30_____700000___C30_IS BLANK
A31_____02/28/2018___B31_IS BLANK___C31_____700000
A32_____02/28/2018___B32_IS BLANK___C32_____500000
A33_____02/28/2018___B33_____1500000___C33_IS BLANK
A34_____02/28/2018___B34_IS BLANK___C34_____3265431.51

Here I showed some sample data, but reality the data is huge.

Please help me to achieve this task.

I am very new in VBA, and I don't know how to do this, please help me.

Thanks
Kashif

 

[kashif.special2005]

Hi,

Kindly request to all of great guys of excel community, please help me on this.

Thanks
Kashif

 

[Trevor G]

In your example you only have one column of dates so your rules don't make sense.

 

[kashif.special2005]

Hi Trevor,

Thanks for reply, let me exaplain.

Rule 1 is saying that "Deposit & withdrawal entry for same date and same amount - color code "green".

that means let say the date "02/07/2018" has two entries and 1 for deposit and 2 is for withdrawal and if both the amount deposit and withdrawal are the same then this will meet "Rule 1" condition because the both the amount are same and the date is also the same, therefore both the cells background color will be green.

I hope, I am able clear your doubt.

Thanks
Kashif

 

[Akuini]

Hi,

Kindly request to all of great guys of excel community, please help me on this.

Thanks
Kashif

Can you post some data sample in a table?
You can post your excel table using some tools. Check here:
https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or if you don't want to use some tool you can copy a simple table from worksheet.

To copy a simple table from worksheet:
1. In your worksheet select the table/range
2. In the menu, select border icon > select All Borders > then copy.
3. Back to the thread > in the reply box paste the table


A simple table would look like this:

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]NAME[/TD]
[TD="class: xl65, width: 64"]ID[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Mayson[/TD]
[TD="class: xl65, width: 64"]A01[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Amaya[/TD]
[TD="class: xl65, width: 64"]A03[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Cayden[/TD]
[TD="class: xl65, width: 64"]A04[/TD]
[/TR]
</tbody>[/TABLE]

 

[Trevor G]

Conditional formatting would work. Highlight the cells B2 + C2 and cells below, Under Home tab, Conditional formatting and New Rule and Formula rule add the following: =$B2=$C2 then colour Green

 

[kashif.special2005]

Hi Trevor/Akuini,

Thanks for reply.

Trevor:- I can not apply conditional formatting because when we copy the conditional formatting cells to another cells conditional formatting not works.

Akuini:-

[TABLE="width: 222"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD][TABLE="width: 222"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Deposit[/TD]
[TD]Withdraw[/TD]
[/TR]
[TR]
[TD="align: right"]2/7/2018[/TD]
[TD="align: right"]8605.59[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/7/2018[/TD]
[TD] [/TD]
[TD="align: right"]8606[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2018[/TD]
[TD] [/TD]
[TD="align: right"]5086[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2018[/TD]
[TD="align: right"]82320[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2018[/TD]
[TD] [/TD]
[TD="align: right"]93534[/TD]
[/TR]
[TR]
[TD="align: right"]2/8/2018[/TD]
[TD="align: right"]11214[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/14/2018[/TD]
[TD] [/TD]
[TD="align: right"]99.93[/TD]
[/TR]
[TR]
[TD="align: right"]2/15/2018[/TD]
[TD] [/TD]
[TD="align: right"]29096[/TD]
[/TR]
[TR]
[TD="align: right"]2/15/2018[/TD]
[TD="align: right"]29096[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/16/2018[/TD]
[TD="align: right"]1000000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/16/2018[/TD]
[TD] [/TD]
[TD="align: right"]1000000[/TD]
[/TR]
[TR]
[TD="align: right"]2/20/2018[/TD]
[TD] [/TD]
[TD="align: right"]1000000[/TD]
[/TR]
[TR]
[TD="align: right"]2/20/2018[/TD]
[TD] [/TD]
[TD="align: right"]750[/TD]
[/TR]
[TR]
[TD="align: right"]2/20/2018[/TD]
[TD="align: right"]1000000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/20/2018[/TD]
[TD="align: right"]750[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018[/TD]
[TD] [/TD]
[TD="align: right"]68720[/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018[/TD]
[TD="align: right"]75000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/21/2018[/TD]
[TD] [/TD]
[TD="align: right"]6280[/TD]
[/TR]
[TR]
[TD="align: right"]2/22/2018[/TD]
[TD="align: right"]200000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/22/2018[/TD]
[TD] [/TD]
[TD="align: right"]200000[/TD]
[/TR]
[TR]
[TD="align: right"]2/26/2018[/TD]
[TD="align: right"]1428193.84[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/26/2018[/TD]
[TD] [/TD]
[TD="align: right"]1428193.84[/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD="align: right"]1500000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/27/2018[/TD]
[TD] [/TD]
[TD="align: right"]1500000[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD] [/TD]
[TD="align: right"]4809.77[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]500000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]3265431.51[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD] [/TD]
[TD="align: right"]1500000[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]700000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD] [/TD]
[TD="align: right"]700000[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD] [/TD]
[TD="align: right"]500000[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD="align: right"]1500000[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2018[/TD]
[TD] [/TD]
[TD="align: right"]3265431.51[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Are these the expected results (color = red)?

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Date​

[/TD]
[TD]

Deposit​

[/TD]
[TD]

Withdrawal​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

07/02/2018​

[/TD]
[TD]

8605,59​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

07/02/2018​

[/TD]
[TD][/TD]
[TD]

8606,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

08/02/2018​

[/TD]
[TD][/TD]
[TD]

5086,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

08/02/2018​

[/TD]
[TD]

82320,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

08/02/2018​

[/TD]
[TD][/TD]
[TD]

93534,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

08/02/2018​

[/TD]
[TD]

11214,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

14/02/2018​

[/TD]
[TD][/TD]
[TD]

99,93​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

15/02/2018​

[/TD]
[TD][/TD]
[TD]

29096,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

15/02/2018​

[/TD]
[TD]

29096,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

16/02/2018​

[/TD]
[TD]

1000000,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

16/02/2018​

[/TD]
[TD][/TD]
[TD]

1000000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

20/02/2018​

[/TD]
[TD][/TD]
[TD]

1000000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

20/02/2018​

[/TD]
[TD][/TD]
[TD]

750,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

20/02/2018​

[/TD]
[TD]

1000000,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

20/02/2018​

[/TD]
[TD]

750,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

21/02/2018​

[/TD]
[TD][/TD]
[TD]

68720,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

21/02/2018​

[/TD]
[TD]

75000,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

21/02/2018​

[/TD]
[TD][/TD]
[TD]

6280,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

22/02/2018​

[/TD]
[TD]

200000,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

22/02/2018​

[/TD]
[TD][/TD]
[TD]

200000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

26/02/2018​

[/TD]
[TD]

1428193,84​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

26/02/2018​

[/TD]
[TD][/TD]
[TD]

1428193,84​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

27/02/2018​

[/TD]
[TD]

1500000,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD]

27/02/2018​

[/TD]
[TD][/TD]
[TD]

1500000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

26​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD][/TD]
[TD]

4809,77​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

27​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD]

500000,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

28​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD]

3265431,51​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

29​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD][/TD]
[TD]

1500000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

30​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD]

700000,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

31​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD][/TD]
[TD]

700000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

32​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD][/TD]
[TD]

500000,00​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

33​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD]

1500000,00​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

34​

[/TD]
[TD]

28/02/2018​

[/TD]
[TD][/TD]
[TD]

3265431,51​

[/TD]
[/TR]
</tbody>[/TABLE]


M.

 

[kashif.special2005]

Hi Marcelo,

Thanks for reply.

However row number 9 and 10 should be green, because this lies in Rule 1, deposit and withdrawal date are same and amount also the same so the color should be green, and row 11 and 12 also will be green because this is also lies in Rule 1.

Thanks
Kashif

 

[Marcelo Branco]

Hi Marcelo,

Thanks for reply.

However row number 9 and 10 should be green, because this lies in Rule 1, deposit and withdrawal date are same and amount also the same so the color should be green, and row 11 and 12 also will be green because this is also lies in Rule 1.

Thanks
Kashif

What about rows 20-21, 22-23, 24-25, 30-31? Green?

M.