Formula for validation data

Question

• Hello , i need put in validation data a formula or vba a code for check the next :

In sheet “Codes” put the code for example in column C “asdf” and in the column J put 2.
In the second sheet “Pool” put the next below:
 Codes Number Asdf 100

I need check for the same code if in column J (“Codes”) is 2 or 3 , the cell (Number) in sheet ”Pool” must have a value greater than zero and less than 101. Otherwise if colunn J in sheet “Codes” have another value , the cell (Number) have the value 0.
Thank you
Wednesday, February 20, 2019 4:37 PM

• Please give this a try...

Right Click on Pool Tab --> View Code --> Paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.

```Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim wsCodes As Worksheet
Dim CodeRng As Range
Dim Code

Set wsCodes = Worksheets("Codes")

'Assuming, on Pool Tab, Column A has Codes and Column B has Number

If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
If Target.Row > 1 Then
If Target <> "" Then
If Target.Offset(0, -1) <> "" Then
Code = Target.Offset(0, -1).Value
Set CodeRng = wsCodes.Range("C:C").Find(what:=Code, lookat:=xlWhole)
If Not CodeRng Is Nothing Then
If wsCodes.Cells(CodeRng.Row, "J") = 2 Or wsCodes.Cells(CodeRng.Row, "J") = 3 Then
If Target < 0 Or Target >= 101 Then
MsgBox "You must enter a number which is greather than 0 and less than 101.", vbExclamation
Target = ""
GoTo Skip
End If
Else
If Target <> 0 Then
MsgBox "You must enter 0 only.", vbExclamation
Target = 0
GoTo Skip
End If
End If
Else
End If
Else
MsgBox "Please input a Code in Column A first.", vbExclamation, "Code Required!"
Target = ""
GoTo Skip
End If
End If
End If
End If
Skip:
Application.EnableEvents = True
End Sub```

Subodh Tiwari (Neeraj) sktneer

• Marked as answer by Friday, February 22, 2019 10:57 AM
Thursday, February 21, 2019 12:07 PM

All replies

• Hi LoganTRK,

Could you provide a decision table (condition table) in order to make your requirements clear, understandable?
It would be like this.

If you cannot insert an image, please share it via cloud storage such as OneDrive, Dropbox, etc.

Regards,

Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

Wednesday, February 20, 2019 11:21 PM
• Please give this a try...

Right Click on Pool Tab --> View Code --> Paste the code given below into the opened code window --> Close the VB Editor --> Save your workbook as Macro-Enabled Workbook.

```Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim wsCodes As Worksheet
Dim CodeRng As Range
Dim Code

Set wsCodes = Worksheets("Codes")

'Assuming, on Pool Tab, Column A has Codes and Column B has Number

If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
If Target.Row > 1 Then
If Target <> "" Then
If Target.Offset(0, -1) <> "" Then
Code = Target.Offset(0, -1).Value
Set CodeRng = wsCodes.Range("C:C").Find(what:=Code, lookat:=xlWhole)
If Not CodeRng Is Nothing Then
If wsCodes.Cells(CodeRng.Row, "J") = 2 Or wsCodes.Cells(CodeRng.Row, "J") = 3 Then
If Target < 0 Or Target >= 101 Then
MsgBox "You must enter a number which is greather than 0 and less than 101.", vbExclamation
Target = ""
GoTo Skip
End If
Else
If Target <> 0 Then
MsgBox "You must enter 0 only.", vbExclamation
Target = 0
GoTo Skip
End If
End If
Else
End If
Else
MsgBox "Please input a Code in Column A first.", vbExclamation, "Code Required!"
Target = ""
GoTo Skip
End If
End If
End If
End If
Skip:
Application.EnableEvents = True
End Sub```

Subodh Tiwari (Neeraj) sktneer

• Marked as answer by Friday, February 22, 2019 10:57 AM
Thursday, February 21, 2019 12:07 PM