Conditional formatting

[RAJESH1960]

Hello guys
I have entered 2 entries in the excel sheet. In the second row the sum of the amounts is zero and hence matched. In the third row the sum of the amounts hasn’t matched. What I am trying to do is I want to use conditional formatting. If the total of any row is not matched, then I want the amount cells of that row to display a color. If I change and correct the amount, then it should remove the color. What formula should I apply in the new rule…?

Query conditional formatting.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	Help	Total
2					Bank	-30010	January	100	February	200	March	300	April	400	May	500	June	600	July	700	August	800	September	900	October	1000	November	1100	December	1200	January	1300	February	1400	March	1500	April	1600	May	1700	June	1800	July	1900	August	2000	September	2100	October	2200	November	2300	December	2400	Round Off	10	Matched	0
3					Bank	-30010	January	100	February	200	March	300	April	400	May	500	June	600	July	700	August	800	September	900	October	1000	November	1100	December	1200	January	1300	February	1400	March	1500	April	1600	May	1700	June	1800	July	1900	August	2000	September	2100	October	2200	November	2300	December	2400	Round Off	-10	Not Matching	-20
PostData

Cell Formulas
Range	Formula
BE2	BE2	=IF(BF2=0,"Matched","DebitCreditNotMatching")
BF2:BF3	BF2	=F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2+AB2+AD2+AF2+AH2+AJ2+AL2+AN2+AP2+AR2+AT2+AV2+AX2+AZ2+BB2+BD2
BE3	BE3	=IF(BF3=0,"Matched","Not Matching")

 

[RAJESH1960]

Sorry The formula in BE2 was not changed. It is =IF(BF2=0,"Matched","Not Matching"). The cells which should color if not matched are F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2+AB2+AD2+AF2+AH2+AJ2+AL2+AN2+AP2+AR2+AT2+AV2+AX2+AZ2+BB2+BD2.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	Help	Total
2					Bank	-30010	January	100	February	200	March	300	April	400	May	500	June	600	July	700	August	800	September	900	October	1000	November	1100	December	1200	January	1300	February	1400	March	1500	April	1600	May	1700	June	1800	July	1900	August	2000	September	2100	October	2200	November	2300	December	2400	Round Off	10	Matched	0
3					Bank	-30010	January	100	February	200	March	300	April	400	May	500	June	600	July	700	August	800	September	900	October	1000	November	1100	December	1200	January	1300	February	1400	March	1500	April	1600	May	1700	June	1800	July	1900	August	2000	September	2100	October	2200	November	2300	December	2400	Round Off	-10	Not Matching	-20
Data

Cell Formulas
Range	Formula
BE2	BE2	=IF(BF2=0,"Matched","DebitCreditNotMatching")
BF2:BF3	BF2	=F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2+AB2+AD2+AF2+AH2+AJ2+AL2+AN2+AP2+AR2+AT2+AV2+AX2+AZ2+BB2+BD2
BE3	BE3	=IF(BF3=0,"Matched","Not Matching")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:BB3	Expression	=AND($BF2<>0,ISEVEN(COLUMN(E2)))	text	NO

 

[RAJESH1960]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	Help	Total
2					Bank	-30010	January	100	February	200	March	300	April	400	May	500	June	600	July	700	August	800	September	900	October	1000	November	1100	December	1200	January	1300	February	1400	March	1500	April	1600	May	1700	June	1800	July	1900	August	2000	September	2100	October	2200	November	2300	December	2400	Round Off	10	Matched	0
3					Bank	-30010	January	100	February	200	March	300	April	400	May	500	June	600	July	700	August	800	September	900	October	1000	November	1100	December	1200	January	1300	February	1400	March	1500	April	1600	May	1700	June	1800	July	1900	August	2000	September	2100	October	2200	November	2300	December	2400	Round Off	-10	Not Matching	-20
Data

Cell Formulas
Range	Formula
BE2	BE2	=IF(BF2=0,"Matched","DebitCreditNotMatching")
BF2:BF3	BF2	=F2+H2+J2+L2+N2+P2+R2+T2+V2+X2+Z2+AB2+AD2+AF2+AH2+AJ2+AL2+AN2+AP2+AR2+AT2+AV2+AX2+AZ2+BB2+BD2
BE3	BE3	=IF(BF3=0,"Matched","Not Matching")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:BB3	Expression	=AND($BF2<>0,ISEVEN(COLUMN(E2)))	text	NO

Very nice Fluff. Perfect as usual. Thanks,

 

[Fluff]

You're welcome & thanks for the feedback.