Highlight cells with particular values

[mandura]

hi folks!

i want to highlight cells in column A when i type or paste particular values in it.

in "styles" - "conditional formatting" - i can choose "highlight cells rule" and then
"equal to" or "text that contains", and it work perfectly.

my problem is that i have a lot of these particular numbers, but i dont see a way
to type in all these particular numbers, and excel wont take it when i choose a range (column)
where all these particular numbers are written in.

in the attached file i want to highlight in column A all cells when i type the numbers in column A from
sheet 2.

can you please tell me how to do that?

thanks in advance!

mandura

[BadlySpelledBuoy]

Create a named range for the values on Sheet2 - In the attached I've called it "NumberRange".

Use a formula to dictate the conditional formatting. I've used

Please Login or Register  to view this content.

Be careful when pasting values in. If you don't use Paste Special / Values you will overwrite the conditional formatting.

Hope that helps.

BSB

[mandura]

great!

you solved my problem!!
thank you very much!!

but, can you please explain my what exactly you have done?

where did you wrote =COUNTIF(NamedRange,A1)>0 in?
how did you create the numberrange?

sorry but i am not that good in excel.

thank you very much for your help!!

[Aardigspook]

This is how it's done:
- Go to Sheet 2 and select the range of numbers you want have checked
- In the white box to the left of the formula bar (which probably reads 'A1'), type in 'NumberRange' - this makes this a 'Named Range'

-- You can check this has worked correctly by clicking 'Name Manager' on the 'Formulas' tab, where you should see an entry called 'Number Range' showing the range it refers to (Sheet2, A1:A15) and the values (115, 116, etc)

- Now go back to Sheet 1 and select your range (A1:A40).
- Click 'Conditional Formatting' on the 'Home' tab, select 'New Rule' and 'Use a formula to determine which cells to format'.
- In the formula box, type =COUNTIF(NumberRange,A1)>0
- Click the 'Format' box and select the formatting you want (blue fill / red text / whatever you want), then click OK twice.

Hope that helps.


As your problem has been solved, please consider giving BadlySpelledBuoy reputation (by clicking the star to the bottom-left of his post) and mark the thread as 'Solved' by using the 'Thread Tools' menu at the top of your first post. Thank you.

[mandura]

thank you very, very much!!

you really helped me!!

[mandura]

thank you very, very much!!

you really helped me!!

[Aardigspook]

You're welcome.