Triggering Alerts based on Cell Values

**AppSupportKarl** · 02-22-2011, 09:12 AM

Hello,

i need an alert that triggers if the the following criteria is met:

IF the value in Cell A1 is not found in a list (e.g. Cells C1:C15) and Cell B1 = 'Mover' or 'Leaver' Then Alert ("The user is not currently on the List of Active Users. Please ensure the name is correct, and that it is spelt correctly')

I have only used Macros that trigger from buttons, so am unsure how to go about this.

Thanks,

**Marcol** · 02-22-2011, 10:26 AM

Try this in the sheet module for the sheet you need to control.

Please Login or Register  to view this content.

To find the correct Sheet Module
1/. In Excel right click on the tab of the sheet you want to use the macro in.
Select View code this will open the VBa editor with the sheet module.

2/. Paste all of the given code in the resultant pane/window.

3/. Save the file and close the VBa editor

Enter a name in A1 and select from the drop-down in B2 to see the result.

Hope this helps

**royUK** · 02-22-2011, 11:14 AM

You could use Conditional Formatting to change the cell colour if the criteria aren't met.

**Marcol** · 02-22-2011, 11:45 AM

Taking Roys' suggestion on board, one way might be

Conditional Formatting > Use a formula to determine whch cells to format
Format values where this formula is true

Please Login or Register  to view this content.

Applies to

Please Login or Register  to view this content.

Format.....
Fill > Red

I have added this to the sample workbook below. (It still has the macro solution in it.)

**AppSupportKarl** · 02-25-2011, 08:17 AM

Hi Marcol.

i'm trying to stay away from conditional formatting, as not everyoone using this spreadsheet will have 07, and i already have a lot of conditional formatting in place!

i've had to jiggle with your code a bit, but i'm now getting a syntax onf the first line?

Please Login or Register  to view this content.

**royUK** · 02-25-2011, 08:18 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Marcol** · 02-25-2011, 09:10 AM

Where do you have the macro? It should be in the sheet module.
I don't understand why that line should throw an error, it's autumatic VBa syntax .

This line however will not work

Please Login or Register  to view this content.

You haven't set a with block or defined a range to search
the line should be like this

Please Login or Register  to view this content.

or safer to tighten the search criteria

Please Login or Register  to view this content.

Change "C:C" to suit the range you need to search, and "C1" to your start cell.

Post a sample of the sheet you are using and I'll fix it for you.

**AppSupportKarl** · 02-25-2011, 09:25 AM

Thanks for your help Marcol,

i''m still struggling, and the file is rather large... detailing around 500 staff members, and all starters, leavers or movers.

I'll create a test environment, and post asap.

here you go, this is a stripped down version of the workbook i'm using. the sheet in question is 'Maintenance' and although i've not amended the code, the list of names is on 'sheet1'