How to pick and assign formula on behalf of cell color

[pankajgrover]

Hi, i am doing copy paste formula according to buy sell option. But i want if cell have green color this Formula =(((F22-L22)*B22)*C22)-AI22+O22-N22 apply or if have red color this Formula =(((F21-L21)*B21)*C21)-H21+AI21 apply in same Net result column. Still now i doing manually and How i do that automatic? I want in below example AI column auto pick formula from cell AG16, AG17 color based .Should i do any conditional formatting ? Thanks

Shares Trading (1).xlsx
	AC	AD	AE	AF	AG		AI
15							Net result
16	Oil	6621.00	23/12/2022	0.5	6500.00		-6050
17	Gas	430.00	23/12/2022	1	410.00		25500
sheet

Cell Formulas
Range	Formula
AI16	AI16	=(((F16-L16)*B16)*C16)-H16+AH16
AI17	AI17	=(((F17-L17)*B17)*C17)-AH17+O17-N17

 

[HongRu]

maybe this helps.
Info only - get.cell arguments

 

[pankajgrover]

maybe this helps.
Info only - get.cell arguments

I did not understand this article very complex. Any easy way to do that ?

 

[Peter_SSs]

How are the cells getting coloured green or red?
- Conditional Formatting?
- vba?
- Manual?
- Something else?

 

[pankajgrover]

How are the cells getting coloured green or red?
- Conditional Formatting?
- vba?
- Manual?
- Something else?

I will do it manual cell color that's no issue. but i want result formula pick from color. Another idea was in my mind that i insert 1 more column and give value S or B sale or Buy , and that defines result formula. But i happy with manual color because its easy for me understand that values green and red means buy and sale data without the need of 1 extra column. Thanks

 

[Peter_SSs]

To me it would make more sense to add that extra column with B or S. That will make the formula choice easy and you can also set up Conditional Formatting to automatically make the row green or red depending on that B or S value in the extra column.

 

[pankajgrover]

To me it would make more sense to add that extra column with B or S. That will make the formula choice easy and you can also set up Conditional Formatting to automatically make the row green or red depending on that B or S value in the extra column.

If i go with extra column how to do that result column pick formula on based of B or S ? if can i also go with color based option formula pick up pls also share how to do that ? I just want formula in auto mode in only AI column in above example . Thanks

 

[Peter_SSs]

Try something like this. Ignore the actual results in column AI as I did not have your values for column B, C, F etc I just made up some numbers to check that my formulas gave the same results as yours.

22 12 25.xlsm
	AC	AD	AE	AF	AG		AI	AJ
15							Net result
16	Oil	6621	44918	0.5	6500		-27	S
17	Gas	430	44918	1	410		41	B
Buy Sell

Cell Formulas
Range	Formula
AI16:AI17	AI16	=(F16-L16)*B16*C16+IF(AJ16="S",-H16+AH16,-AH16+O16-N16)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AE16:AJ17	Expression	=$AJ16="B"	text	NO
AE16:AJ17	Expression	=$AJ16="S"	text	NO

 

[pankajgrover]

Try something like this. Ignore the actual results in column AI as I did not have your values for column B, C, F etc I just made up some numbers to check that my formulas gave the same results as yours.

22 12 25.xlsm
	AC	AD	AE	AF	AG		AI	AJ
15							Net result
16	Oil	6621	44918	0.5	6500		-27	S
17	Gas	430	44918	1	410		41	B
Buy Sell

Cell Formulas
Range	Formula
AI16:AI17	AI16	=(F16-L16)*B16*C16+IF(AJ16="S",-H16+AH16,-AH16+O16-N16)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AE16:AJ17	Expression	=$AJ16="B"	text	NO
AE16:AJ17	Expression	=$AJ16="S"	text	NO

This is working . Thanks. If i opt for cell color instead of creating new column B or S. I just asking for knowledge purpose , Like below what will be code for replace Bold words
=(F16-L16)*B16*C16+IF(AG16="Cell color is red",-H16+AH16,-AH16+O16-N16)

 

[Peter_SSs]

You would need to use vba not standard worksheet functions.