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
Try the following in the sheet code module of the sheet that you want to enforce this condition on.
[vba]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[/vba]
_________________________________________________________________________
"In theory there is no difference between theory and practice. In practice there is." - Chuck Reid
Any day you learn something new is a day not wasted.
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
[VBA]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[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
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
We are here to assist; not just to provide solutions. Please post the code you have attempted to solve this.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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) [VBA]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 [/VBA]
Posting Permissions
Reply With Quote
