Conditional Data Validation issue

[nikki333]

Hi Folks

I'm trying to write a data validation rule via VBA.

The purpose should be to check whether a certain value/text is present in one cell of row (let's say X), and if so then to allow only "x" or "" for the rest of that row.

My try for the formula in the data verification goes as follows:

=IF(B$4 = "Standard"; OR("x";""))

(the logik should be like: if cell B4="Standard" then allowed values are either "x" or ""

Many thanks,
Cheerio

[mancubus]

this is a vba solution:
right click on the sheet name
click "View Code"
paste the code in the sheet's code module

Private Sub Worksheet_Change(ByVal Target As Range)    
    With Target
        If Application.CountIf(Rows(.Row), "Standard") = 0 Then Exit Sub
        If .Value = "x" Or .Value = "" Then Exit Sub
        MsgBox "The row of the cell you're trying to insert text contains at least one cell whose value is ""Standard"". You can only input ""x"" into the cell or leave it blank.", vbCritical + vbOKOnly
        .ClearContents
    End With
End Sub