Solved: If in particular column written text then other column should be numeric

[vipulhumein]

if in any cell of column A i wrote text "FOC" then active cell of column B should have only numeric value and if in any cell of column A i wrote any numeric value then active cell of column B should have "FOC" ...

how this can be happen please help
THANKS IN ADVANCE

[Teeroy]

Try the following in the sheet code module of the sheet that you want to enforce this condition on.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If Target.Value = "FOC" Then
Target.Offset(0, 1).ClearContents
With Target.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween, Formula1:="0", Formula2:="1600000" 'adjust min and max as required
.IgnoreBlank = True
.ErrorTitle = "Invalid Data Entry"
.ErrorMessage = "This data must be a number greater than, or equal to, 0."
.ShowError = True
End With
Else
Target.Offset(0, 1).Validation.Delete
End If
If WorksheetFunction.IsNumber(Target.Value) Then
Target.Offset(0, 1) = "FOC"
End If
End If
End Sub

[vipulhumein]

hi teeroy
Can u please tell me if my target column is AE
and i have column V should act according to column AE than how should the above vba will be rectified

[mdmackillop]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tgt As Long, Rslt As Long

Tgt = 31
Rslt = -9

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = Tgt Then
If Target.Value = "FOC" Then
Target.Offset(0, Rslt).ClearContents
With Target.Offset(0, Rslt).Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween, Formula1:="0", Formula2:="1600000" 'adjust min and max as required
.IgnoreBlank = True
.ErrorTitle = "Invalid Data Entry"
.ErrorMessage = "This data must be a number greater than, or equal to, 0."
.ShowError = True
End With
Else
Target.Offset(0, Rslt).Validation.Delete
End If
If WorksheetFunction.IsNumber(Target.Value) Then
Target.Offset(0, Rslt) = "FOC"
End If
End If
End Sub

[vipulhumein]

hi mdmackillop
can u help me if the target column is in sheet1 and the result column in sheet2 column k then what will be the vba for this

please help me

[vipulhumein]

hi mdmackillop
if the target column is in sheet1 and the result column is in sheet2 (ie in column K) then what will be the vba

[mdmackillop]

We are here to assist; not just to provide solutions. Please post the code you have attempted to solve this.

[vipulhumein]

hi mdmackillop
how to rectify this code if the target column is in sheet1 and the result column is in sheet2 (ie in column K) Private Sub Worksheet_Change(ByVal Target As Range)
Dim Tgt As Long, Rslt As Long

Tgt = 31
Rslt = -9

If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = Tgt Then
If Target.Value = "FOC" Then
Target.Offset(0, Rslt).ClearContents
With Target.Offset(0, Rslt).Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween, Formula1:="0", Formula2:="1600000" 'adjust min and max as required
.IgnoreBlank = True
.ErrorTitle = "Invalid Data Entry"
.ErrorMessage = "This data must be a number greater than, or equal to, 0."
.ShowError = True
End With
Else
Target.Offset(0, Rslt).Validation.Delete
End If
If WorksheetFunction.IsNumber(Target.Value) Then
Target.Offset(0, Rslt) = "FOC"
End If
End If
End Sub