locate a value among many

Hi Martindelica,

Try this code:

Please Login or Register  to view this content.

Actually, it works great on the sheet I posted, but when I copy it into my other sheet I get an error 13. It has a problem with this line " If ce2.Value = ce.Value Then".

I got it. Just got two other things. Can I get the macro to run automatically without having to goto tools/macros and run. Cant it run automatically in the background?

And is there somethin gI can add to the code that will hihglight the cell even though the number is not an exact match. Like if I enter in cell A1 to allowance both sides of the exact number. EG If I want to see the values that are a match of 25 and colro them red, and in cell A1, I put a 2, then if the number is 23, 24, 25, 26, 27 the cell will light up red.

Kindest regards,
Martin.

Hi Martin,

Try this code, it should do both requests (automated & color range of cells based on value in cell A1):

Please Login or Register  to view this content.

Put this code into the 'ThisWorkbook' module in the VB Editor.
- Press ALT+F11 to open VB Editor
- In left pane, double-click 'ThisWorkbook'
- Copy & Paste the above code
- Remove any previous code you had added for this issue (from ThisWorkbook or from any specific sheet's code)
- Close the VB Editor and test.