highlighting numbers in tens position

[rzml]

Hi,
looking for a vba code that will highlight the numbers in a range of cells containing 3,4,8 or 9 in the Tens position.
Thanks for the help.

 

[jasonb75]

You could do that with simple conditional formatting using a rule of

=OR(MOD(INT(A1/10),10)={3,4,8,9})

where A1 is the cell to highlight.

 

[rzml]

thanks for your answer jasonb, but i need this to be done for selected cells such as below

 

[jasonb75]

You could still do it the same way, with the range selected in your example above you just need to change A1 to M2 in the formula then apply it to the whole range.

 

[rzml]

it gave an error message

 

[jasonb75]

That's my bad, I was sure that the method could be used in CF but not in data validation.
Try this one instead (I haven't tested this but it should work, it doesn't use any of the methods that are not permitted).

=CHOOSE(MOD(INT(A1/10),10)+1,,,,1,1,,,,1,1)

 

[rzml]

it didn't give an error but nothing happened

 

[jasonb75]

Using a random number list, it works for me.

Book1
	M
2	47
3	43
4	50
5	23
6	43
7	23
8	42
9	18
10	92
11	37
12	12
13	41
14	44
15	58
16	91
17	68
18	4
19	48
20	74
21	96
22	92
23	22
Sheet3

 

[rzml]

oh yes, it did thanks. if I want it to highlight the numbers ones position how can I change the formulae?

 

[jasonb75]

Actually, a slightly easier to follow formula, the position is set by the division part of the formula
ones (see note below)

=SEARCH(MOD(INT(M2/1),10),"3489")

tens

=SEARCH(MOD(INT(M2/10),10),"3489")

hundreds

=SEARCH(MOD(INT(M2/100),10),"3489")

Note that INT and the division part are not necessary for the ones formula but I've left them in so that it is easier to follow. This one could be changed to

=SEARCH(MOD(M2,1),"3489")

and it would still work the same.