Dependant on A1 validate the input on range C1:C100

[omairhe]

Hello,

On range C1:C100
How to allow only positive whole numbers when A1 = not blank or A1 = not "A"
If A1 = A then allow both positive and negative whole numbers only
and If A1 = blank then do not allow anything.

How can I achieve this via Code or maybe data validation if that is even possible?

Thank you millions.

 

[JoeMo]

Untested, but here's some sheet code you can try.
To install sheet code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Range("C1:C100")) Is Nothing Then
    For Each c In Intersect(Target, Range("C1:C100"))
        If IsEmpty(c) Then Exit Sub
        If IsEmpty(Range("A1")) Then
            Application.EnableEvents = False
            Application.Undo
            MsgBox "entry in C1:C100 not allowed while A1 is blank"
            Application.EnableEvents = True
            Exit Sub
        End If
        If [A1] <> "A" Then
            If c.Value < 0 Or c.Value <> Int(c.Value) Or Not IsNumeric(c.Value) Then
                Application.EnableEvents = False
                Application.Undo
                MsgBox "only positive whole numbers allowed in C1:C100 while A1 is not ""A"""
                Application.EnableEvents = True
                Exit Sub
            End If
        End If
        If [A1] = "A" Then
            If Not IsNumeric(c.Value) Or c.Value <> Int(c.Value) Then
                Application.EnableEvents = False
                Application.Undo
                MsgBox "only positive and negative whole numbers allowed in C1:C100 while A1 is ""A"""
                Application.EnableEvents = True
            End If
        End If
    Next c
End If
End Sub