Conditional Formatting using formula range does not work

[pto160]

I am trying to use conditional formatting using a formula with a range. I am not getting the results that I want. Can you use a formula with a range for conditional formatting:
The condition is numbers with the last two numbers after the decimal point that end in a odd number and any cell that has N.

Book1
	A	B	C	D	E	F
1	What I am getting
2	156.23		N	Y	N	TRUE
3	180		N	N	N
4	192.56		Y	Y	N
5
6	What I want
7	156.23		N	Y	N
8	180		N	N	N
9	192.56		Y	Y	N
Sheet1

Cell Formulas
Range	Formula
F2	F2	=OR(1*RIGHT(FIXED(A2,2,1),2)>0,C2="N",D2="N",E2="N")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E4	Expression	=OR(1*RIGHT(FIXED(A2,2,1),2)>0,C2="N",D2="N",E2="N")	text	NO

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
2	156.23		N	Y	N	TRUE
3	180		N	N	N
4	192.56		Y	Y	N
Sheet14

Cell Formulas
Range	Formula
F2	F2	=OR(1*RIGHT(FIXED(A2,2,1),2)>0,C2="N",D2="N",E2="N")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F4	Expression	=OR(A2="N",ISNUMBER(FIND(RIGHT(FIXED(A2,2)),"13579")))	text	NO

Do you really want A4/A9 highlighted since it ends in an even number?

 

[Joe4]

The condition is numbers with the last two numbers after the decimal point that end in a odd number and any cell that has N.

In listing your conditions, you used AND (meaning, both conditions must be met).
But in your formula, you are using OR, which means only one of those conditions must be met.)
If you truly meant AND, then you should be using an AND formula, and not an OR one.

 

[pto160]

Sorry, I gave the wrong explanation for the fixed formula.
I wrote the conditional formatting formula based only on numbers that are not .00 after the decimal point.
So 156.23 or 192.56, I want highlighted, but not 180.00 or any number after the decimal point that ends in .00
Eric W or Joe4, Will that change the formula above?
My apologies once again.

 

[jtakw]

Sorry, I gave the wrong explanation for the fixed formula.
I wrote the conditional formatting formula based only on numbers that are not .00 after the decimal point.
So 156.23 or 192.56, I want highlighted, but not 180.00 or any number after the decimal point that ends in .00

Hi,

Try this:

Book3.xlsx
	A	B	C	D	E
1	What you want
2	156.23		N	Y	N
3	180		N	N	N
4	192.56		Y	Y	N
Sheet1041

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:E4	Expression	=OR(A2="N",IF(N(A2),MOD(A2,1),0))	text	NO

 

[pto160]

Thanks jtakw. It is highlighting the correct columns, but also an additional column I don't want highlighted.
I went back to my work data and it is highlighting a column that has a date and time in the cell. I wonder why that is?

Book1
	A	B	C	D	E	F
1	What you want
2	156.23		1/11/2022 20:31	N	Y	N
3	180.18		5/11/2022 12:31	N	y	N
4	192.56		12/11/2022 12:31	Y	Y	N
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F4	Expression	=OR(A2="N",IF(N(A2),MOD(A2,1),0))	text	NO

​

 

[jtakw]

Thanks jtakw. It is highlighting the correct columns, but also an additional column I don't want highlighted.
I went back to my work data and it is highlighting a column that has a date and time in the cell. I wonder why that is?

Book1
	A	B	C	D	E	F
1	What you want
2	156.23		1/11/2022 20:31	N	Y	N
3	180.18		5/11/2022 12:31	N	y	N
4	192.56		12/11/2022 12:31	Y	Y	N
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F4	Expression	=OR(A2="N",IF(N(A2),MOD(A2,1),0))	text	NO

​

Date and Time values are Numbers in Excel, that's the reason, you should've shown that in OP.

I'll look into it.

EDIT: How large could the numbers be in Column A ?

 

[pto160]

Thanks jtakw for the explanation. You are completely correct. Based on your answer, yes the date and time would have numbers after the decimal point.

 

[jtakw]

I edited my post above.

How large can the numbers be in Column A.
And, what are the typical Date values in Column C, (e.g. Year 2000 and later, etc.)

 

[jtakw]

Another question, are the Date values Only in Column C, in your actual data, if so, this would work:

Book3.xlsx
	A	B	C	D	E	F
1	What you want
2	156.23		1/11/22 20:31	N	Y	N
3	180.18		5/11/22 12:31	N	y	N
4	192.56		12/11/22 12:31	Y	Y	N
Sheet1042

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:F4	Expression	=OR(A2="N",IF(AND(N(A2),COLUMN(A2)<>3),MOD(A2,1),0))	text	NO