Message box if no value.

[adamsm]

Hi,

How could I create a message box saying "value does not exist" if the value that I write in sheet1 cell A1 does not contain in any cell of the sheet 2.

Any help would be kindly appreciated.

[Simon Lloyd]

try this, it goes in the worksheet code module taht you are working with[vba]Private Sub Worksheet_Change(ByVal Target As Range)
Dim TgtCount
TgtCount = Application.WorksheetFunction.CountIf(Sheets("Sheet2").UsedRange, Target.Value)
If TgtCount = 0 Then
MsgBox "Value does not exists in sheet2"
Else
MsgBox "Value exists in sheet2"
Target.ClearContents
Target.Select
End If
End Sub[/vba]
How to Save a Worksheet Event Macro
1. Copy the macro above by holding down Ctrl then left click to the left of the code window, then Right Click selected code and Copy.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

[adamsm]

Thanks for the help. How could I change the code as a standard module code?

Any help would be appreciated.

[Simon Lloyd]

Change "Target" for "Activecell" and then cut the body of the code out and put in a normal sub, however if you do that you will not get notification if the value exists or not as the code will only run manually i.e by clicking a button with the code assigned or going to TOOLS>MACROS>RUN and run your macro.

[adamsm]

Thanks for the help. I do appreciate it.