conditional formatting for changing cell

[rzml]

Hi,
I Use this =OR((MOD(O2,10)=3),(MOD(O2,10)=4),(MOD(O2,10)=8),(MOD(O2,10)=9))
conditional formatting equation to highlight cells with 3,4,8 and 9 in the ones position. but the equation is fixed on the O. Is there a way the equation will change depending on the selected cells?

testing vba.xlsx
	M
1	Total
2	0
3	47
4	49
5	58
6	56
7	69
8	76
9	54
10	82
11	67
12	62
13	59
14	58
15	76
16	59
17	75
18	56
19	48
20	71
21	94
22	78
23	79
24	68
25	63
26	73
27	91
28	76
29	97
30	63
31	98
32	86
33	94
34	79
35	67
36	83
37	81
38	83
39	74
40	74
41	82
42	74
43	82
44	93
45	60
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2	Expression	=OR((MOD(activecell,10)=3),(MOD(activecell,10)=4),(MOD(activecell,10)=8),(MOD(activecell,10)=9))	text	NO

thanks

 

[etaf]

that will move depending on the selection
As you start at ROW 2 - then the selection needs to start at row 2

If you wanted Column O to column S and Rows 2 to 1000
then select O2:S1000
then the formula will look at those cells

NOTE to fix the column you need a $

so $O2 fixed the column to O

 

[rzml]

thanks for the answer but I can't use O2:S1000 since it will contain other information. I can't fix it to O using $ since it can change
the only thing I can use is the header ie "Total".

 

[etaf]

I have setup a simple example where 1 will be highlighted , BUT only on a column that has Total in Row1
So this may do you

=AND( O$1="Total",OR((MOD(O1,10)=3),(MOD(O1,10)=4),(MOD(O1,10)=8),(MOD(O1,10)=9)))
But I'm not sure what your range of columns are - lets say it was possible to be A to Z
Then select A to Z
and use
=AND( A$1="Total",OR((MOD(A1,10)=3),(MOD(A1,10)=4),(MOD(A1,10)=8),(MOD(A1,10)=9)))

But I assume the sheet changes somehow for the column to change , and so you may need VBA

Book1
	A	B	C	D
1	X	Y	Total	Z
2	1	1	1	1
3
4	1		1
5
6	3	1	2
7
8
9			1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:D	Expression	=AND(A$1="Total", A1=1)	text	NO

 

[rzml]

I have setup a simple example where 1 will be highlighted , BUT only on a column that has Total in Row1
So this may do you

=AND( O$1="Total",OR((MOD(O1,10)=3),(MOD(O1,10)=4),(MOD(O1,10)=8),(MOD(O1,10)=9)))
But I'm not sure what your range of columns are - lets say it was possible to be A to Z
Then select A to Z
and use
=AND( A$1="Total",OR((MOD(A1,10)=3),(MOD(A1,10)=4),(MOD(A1,10)=8),(MOD(A1,10)=9)))

But I assume the sheet changes somehow for the column to change , and so you may need VBA

Book1
	A	B	C	D
1	X	Y	Total	Z
2	1	1	1	1
3
4	1		1
5
6	3	1	2
7
8
9			1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:D	Expression	=AND(A$1="Total", A1=1)	text	NO

Thanks, Etaf that worked fine with me.

 

[etaf]

you are welcome