Excel VBA/Formula to perform operation in a cell based on cell Colour

[erbhaskar95]

Hello there,

Here, we have a Red and White Coloured cells in "Remarks" Column. The white coloured cells are fine and need not to be worried about.

As shown, there is a continued red coloured cells upto S.N 5 Row starting from S.N No 2 Row. So, I want to copy its corresponding "Ending" Column Value one cell above the starting point of Red Coloured Cell in "Remarks" Column.
Similarly, I want to put "Ending" Column value from S.N 8 Row to S.N No 7 Row (as there is only 1 Red coloured cell in a stretch)
Similarly, I want to put "Ending" Column value from S.N 22 Row to S.N No 20 Row (as there is 2 Red coloured cell in a stretch)
And so on depending on how may red coloured cells are there in a stretch in my entire file.

Is it possible to do this using VBA or any method?

sfsf.xlsm
	AH	AI	AJ	AK
1	S.N	Starting	Ending	Condition
2	1	178	265
3	2	306	368	41
4	3	343	412	-25
5	4	388	454	-24
6	5	415	482	-39
7	6	732	802	250
8	7	1033	1095	231
9	8	1086	1151	-9
10	9	1313	1418	162
11	10	4075	4135	2657
12	11	4233	4294	98
13	12	4700	4759	406
14	13	4916	4977	157
15	14	5056	5111	79
16	15	5284	5347	173
17	16	5408	5468	61
18	17	5595	5651	127
19	18	6084	6141	433
20	19	6271	6334	130
21	20	6494	6580	160
22	21	6559	6616	-21
23	22	6648	6730	32
24	23	6804	6871	74
25	24	7064	7135	193
26	25	7686	7745	551
27	26	7794	7859	49
28	27	8037	8093	178
29	28	8103	8182	10
30	29	8273	8342	91
31	30	8796	8859	454
32	31	8827	8897	-32
33	32	8915	9040	18
34	33	9315	9383	275
Chainage

Cell Formulas
Range	Formula
AK3:AK34	AK3	=AI3-AJ2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AK3:AK101	Expression	=AK3<50	text	NO