Conditional formatting gone wrong

[RAJESH1960]

Hello guys

I am getting a few problems in my sheet. 1. Rows 5 & 7 are matched but selected rows are colored as rule applied in conditional formatting, which it shouldn’t. 2. Row 6 is matched but it is showing Debit / Credit Not Matching.

Otherwise, all other remaining rows are correct.

Query Amount mismatch wrong formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Help	Date*	Type*	Optional	Optional	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name	Amt		Total
2	Matched	03-04-2021	Payment			Kotak Bank	2000	January	-1000	February	-1000				0.00
3	Matched	05-04-2021	Payment			Kotak Bank	5664	February	-4800	March	-864				0.00
4	Matched	05-04-2021	Receipt			Kotak Bank	-42000	March	20000	April	22000				0.00
5	Matched	05-04-2021	Receipt			Kotak Bank	-43	April	5	May	5	March	33		0.00
6	Debit/Credit Not Matching	06-04-2021	Receipt			Kotak Bank	-16376	May	16376.42	June	-0.42				0.00
7	Matched	08-04-2021	Payment			Kotak Bank	10000	June	-5000	July	-5000				0.00
8	Debit/Credit Not Matching	08-04-2021	Payment			Kotak Bank	9000	June	-5000	July	-5000				-1000.00
PasteData

Cell Formulas
Range	Formula
A2:A8	A2	=IF(B2&C2&F2&G2&H2&I2="","",IF(O2=0,"Matched","Debit/Credit Not Matching"))
O2:O8	O2	=IF(B2="","",G2+I2+K2+M2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:1048576	Expression	=AND($O2<>0,ISEVEN(COLUMN(B2)))	text	NO
E2:M7,F8,H8:K8	Expression	=AND($N2<>0,ISEVEN(COLUMN(B2)))	text	NO

 

[RAJESH1960]

Earlier I had selected the whole sheet. But after selecting this range F2:M100 and then after applying the rule I got it partly correct. Still the row 6 is showing Not Matching whereas it has to show matched.

Query Amount mismatch wrong formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Help	Date*	Type*	Optional	Optional	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name	Amt		Total
2	Matched	03-04-2021	Payment			Kotak Bank	2000	January	-1000	February	-1000				0.00
3	Matched	05-04-2021	Payment			Kotak Bank	5664	February	-4800	March	-864				0.00
4	Matched	05-04-2021	Receipt			Kotak Bank	-42000	March	20000	April	22000				0.00
5	Matched	05-04-2021	Receipt			Kotak Bank	-43	April	5	May	5	March	33		0.00
6	Debit/Credit Not Matching	06-04-2021	Receipt			Kotak Bank	-16376	May	16376.42	June	-0.42				0.00
7	Matched	08-04-2021	Payment			Kotak Bank	10000	June	-5000	July	-5000				0.00
8	Debit/Credit Not Matching	08-04-2021	Payment			Kotak Bank	9000	June	-5000	July	-5000				-1000.00
9
PasteData

Cell Formulas
Range	Formula
A2:A9	A2	=IF(B2&C2&F2&G2&H2&I2="","",IF(O2=0,"Matched","Debit/Credit Not Matching"))
O2:O9	O2	=IF(B2="","",G2+I2+K2+M2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:M9	Expression	=AND($O2<>0,ISEVEN(COLUMN(B2)))	text	NO

 

[etaf]

as the formula starts at row2 , so does the selection

in a new column row 2 put the formula, and copy to the same range , see if you get true false correctly

=AND($O2<>0,ISEVEN(COLUMN(B2)))

see if you get TRUE False

Column O is fixed with the $
BUT B2 will move

So only ROW 6 & 8 can be possibly TRUE as the the others have a zero in column O

then ISEVEN(COLUMN(B2)))
Means only , B is Even , so thats going to be column F as thats the start of selection
then every other column F,H,J,L etc

so its working as expected

BUT WHAT do you want to highlight and why ?

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Help	Date*	Type*	Optional	Optional	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name	Amt		Total
2	Matched	44289	Payment			Kotak Bank	2000	January	-1000	February	-1000				0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
3	Matched	44291	Payment			Kotak Bank	5664	February	-4800	March	-864				0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4	Matched	44291	Receipt			Kotak Bank	-42000	March	20000	April	22000				0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
5	Matched	44291	Receipt			Kotak Bank	-43	April	5	May	5	March	33		0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
6	Debit/Credit Not Matching	44292	Receipt			Kotak Bank	-16376	May	16376.42	June	-0.42				7.27751E-14		TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
7	Matched	44294	Payment			Kotak Bank	10000	June	-5000	July	-5000				0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
8	Debit/Credit Not Matching	44294	Payment			Kotak Bank	9000	June	-5000	July	-5000				-1000		TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
9																	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
Q2:W9	Q2	=AND($O2<>0,ISEVEN(COLUMN(B2)))
A2:A9	A2	=IF(B2&C2&F2&G2&H2&I2="","",IF(O2=0,"Matched","Debit/Credit Not Matching"))
O2:O9	O2	=IF(B2="","",G2+I2+K2+M2)

 

[Toadstool]

Hi Rajesh1960,

The highlit cells were set using manual fill, not Conditional Formatting. Are the ones in yellow the ones you want highlighted apart from row 6?

Row 6 will be highlighted because O6 is not zero, it contains 0.000000000000073 so you may want to use the ROUND function.

 

[RAJESH1960]

as the formula starts at row2 , so does the selection

in a new column row 2 put the formula, and copy to the same range , see if you get true false correctly

=AND($O2<>0,ISEVEN(COLUMN(B2)))

see if you get TRUE False

Column O is fixed with the $
BUT B2 will move

So only ROW 6 & 8 can be possibly TRUE as the the others have a zero in column O

then ISEVEN(COLUMN(B2)))
Means only , B is Even , so thats going to be column F as thats the start of selection
then every other column F,H,J,L etc

so its working as expected

BUT WHAT do you want to highlight and why ?

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Help	Date*	Type*	Optional	Optional	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name	Amt		Total
2	Matched	44289	Payment			Kotak Bank	2000	January	-1000	February	-1000				0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
3	Matched	44291	Payment			Kotak Bank	5664	February	-4800	March	-864				0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
4	Matched	44291	Receipt			Kotak Bank	-42000	March	20000	April	22000				0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
5	Matched	44291	Receipt			Kotak Bank	-43	April	5	May	5	March	33		0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
6	Debit/Credit Not Matching	44292	Receipt			Kotak Bank	-16376	May	16376.42	June	-0.42				7.27751E-14		TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
7	Matched	44294	Payment			Kotak Bank	10000	June	-5000	July	-5000				0		FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
8	Debit/Credit Not Matching	44294	Payment			Kotak Bank	9000	June	-5000	July	-5000				-1000		TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
9																	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
Q2:W9	Q2	=AND($O2<>0,ISEVEN(COLUMN(B2)))
A2:A9	A2	=IF(B2&C2&F2&G2&H2&I2="","",IF(O2=0,"Matched","Debit/Credit Not Matching"))
O2:O9	O2	=IF(B2="","",G2+I2+K2+M2)

I want the row containing amounts which are not matching to be highlighted. I have corrected it but now my only question is why is the 6th row highlighted even when the sum of the amount is 0.

Query Amount mismatch wrong formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Help	Date*	Type*	Optional	Optional	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name*	Amt*	Ledger Name	Amt		Total
2	Matched	03-04-2021	Payment			Kotak Bank	2000	January	-1000	February	-1000				0.00
3	Matched	05-04-2021	Payment			Kotak Bank	5664	February	-4800	March	-864				0.00
4	Matched	05-04-2021	Receipt			Kotak Bank	-42000	March	20000	April	22000				0.00
5	Matched	05-04-2021	Receipt			Kotak Bank	-43	April	5	May	5	March	33		0.00
6	Debit/Credit Not Matching	06-04-2021	Receipt			Kotak Bank	-16376	May	16376.42	June	-0.42				0.00
7	Matched	08-04-2021	Payment			Kotak Bank	10000	June	-5000	July	-5000				0.00
8	Debit/Credit Not Matching	08-04-2021	Payment			Kotak Bank	9000	June	-5000	July	-5000				-1000.00
PasteData

Cell Formulas
Range	Formula
A2:A8	A2	=IF(B2&C2&F2&G2&H2&I2="","",IF(O2=0,"Matched","Debit/Credit Not Matching"))
O2:O8	O2	=IF(B2="","",G2+I2+K2+M2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:O602	Expression	=AND($O2<>0,ISEVEN(COLUMN(B2)))	text	NO

.
The reason for the highlight is I can make the required changes if there is a mistake in the total of that particular row. Like, If I correct the amount in cell G8 from 9000 to 10000 the formatting disappears.

 

[RAJESH1960]

You were right

Hi Rajesh1960,

The highlit cells were set using manual fill, not Conditional Formatting. Are the ones in yellow the ones you want highlighted apart from row 6?

Row 6 will be highlighted because O6 is not zero, it contains 0.000000000000073 so you may want to use the ROUND function.

You are right. Round function solved the problem. Thanks.

 

[Toadstool]

You were right

You are right. Round function solved the problem. Thanks.

You're welcome!
Thanks for letting us know.