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

 

[pto160]

The dates are usually the last 3 days from today's day. So todays date is March 11, 2022. So, I would be looking at March 8, 9, 10, 11.
The maximum amount in column A would never be more than $10,000.
There is two files, so the date column with time would be in different columns.

 

[jtakw]

The dates are usually the last 3 days from today's day. So todays date is March 11, 2022. So, I would be looking at March 8, 9, 10, 11.
The maximum amount in column A would never be more than $10,000.
There is two files, so the date column with time would be in different columns.

Ok, we could check for the $ values for Not over something like 40000 ( which is a date value of 7/6/2009 )

But I think the better approach would be to check for the Date column.
In your 2 files, what are the Date Columns ?

 

[pto160]

So the two files for example, the A2 is the values and J2 for the values in the other file.
The date column would be in column C as per the example above and in column E for the other column in the other file.

 

[jtakw]

Thanks for the info.

Change the A8 cell reference of CF formula for File 2 to the Starting cell of your File 2:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J
1	File 1
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
5
6
7	File 2
8	N	Y	N		1/11/2022					156.23
9	N	y	N		5/11/2022					180.18
10	Y	Y	N		12/11/2022					192.56
Sheet1042

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

 

[pto160]

jtakw, that is a brilliant solution using the mod and column COLUMN(A8)<>5) for example to exclude columns.
I used the formula and I was able to exclude two columns that have dates and times in columns.
I think this is correct?

Book1
	A	B	C	D	E	F
1	File 1
2	156.23	1/11/2022	1/11/2022	N	Y	N
3	180.18	5/11/2022	5/11/2022	N	y	N
4	192.56	12/11/2022	12/11/2022	Y	Y	N
Sheet5

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

 

[jtakw]

That looks correct to me, if your have Dates in Both Columns B and C.

 

[pto160]

Thank you so much for your help. ?
This will save me so much time

 

[jtakw]

You're welcome, thanks for the feedback, glad it works for you.