Macro to search if range contains certain value, and change cell value and color

[craignet]

Hi all, please bare with me as my first time posting, I am a novice at excel and VB but know the basics, any help is much appreciated.

What I am after is a macro, which once played, will check if the values entered in cell (A1) on the active worksheet (Sheet1), are contained in column (A1:A2000) in a different worksheet (Sheet2).

If they are, the macro changes a cell (B2) on the active worksheet (Sheet1) to 'WARNING', however if they are not they change cell (B2) to 'OK'.

Also, if cell (B2) displays 'WARNING' if will fill red, if it contains the word 'OK' it will fill green.

Thank you for reading my post and if you need any more information please let me know.

Excel version 2010, windows 7 home professional.

 

[Sancdar]

Are you sure you want to use a macro? I feel like this can be done simply with a formula and the use of conditional formatting.
In B2 on Sheet1, enter =IFERROR(IF(MATCH(A1,Sheet2!A1:A2000,0),"WARNING"),"OK"). Then under the Home ribbon, set conditional formatting to fill red if the cell contains WARNING and green if it has OK.

The MATCH looks for A1's value in the range specified on Sheet2, returns "OK" if it exists, and if it can't find the value it returns an error which gets caught by the IFERROR.

Hope that helps!

 

[craignet]

Are you sure you want to use a macro? I feel like this can be done simply with a formula and the use of conditional formatting.
In B2 on Sheet1, enter =IFERROR(IF(MATCH(A1,Sheet2!A1:A2000,0),"WARNING"),"OK"). Then under the Home ribbon, set conditional formatting to fill red if the cell contains WARNING and green if it has OK.

The MATCH looks for A1's value in the range specified on Sheet2, returns "OK" if it exists, and if it can't find the value it returns an error which gets caught by the IFERROR.

Hope that helps!

Thank you so much that's exactly what I was looking for.

Could you help me on one other thing, on the IFERROR, is there a way where I could search if the value contains just some of the values in the range. i.e. if in cell A1 I enter SP10 and in the range on Sheet2 it had SP105EK it would still come back as 'WARNING'?

Thanks again for your reply.

 

[Sancdar]

I think you can change the MATCH(A1,....) to MATCH("*"&A1&"*",....) instead. Those asterisks are wildcards that should allow anything, including nothing, to be on the left and right of the contents of cell A1.

 

[craignet]

I think you can change the MATCH(A1,....) to MATCH("*"&A1&"*",....) instead. Those asterisks are wildcards that should allow anything, including nothing, to be on the left and right of the contents of cell A1.

Thanks Sancdar, i'm still having trouble though as not sure if what I am after is possible - a bit more complex than I first thought.

Example of what I need below:

The first letters and first number of a postcode/zipcode is entered in cell B1 (i.e. SP1), if this is part of a postcode/zipcode in range A:A (i.e. SP10 5EE), then in cell C1 it would show 'WARNING' or 'OK' if it is not.

If someone enters (SP) in cell B1 then it would return as 'OK', however if someone was to enter (SP1) it would return 'WARNING'.

Column A
SP10 5EE
RG25 8GG
ET79 8HH
S1 6RG
p9 9PR
ER18 9GH

Currently if I enter any letter and it is in Range A:A then it returns 'WARNING' which is not what i'm after.

As I say I'm not sure if this would be possible, however if there is anyone out there who could give me a few pointers it would be much appreciated.