priya_anthony
Board Regular
- Joined
- Jul 17, 2007
- Messages
- 64
Hi experts need your help i m using the below code to to check the validations of all the column in one particular row and that is working fine but now what i need is how can i change the color of the particular cell where the error has occured
i.e before the message box is displayed i need to change the color of the cell
Private Sub CommandButton1_Click()
Dim cel As Range
Dim level As String
Dim level1 As Range
Dim WBSElements As String
Dim LineText As String
Dim Type1 As String
Dim PE As String
Dim Acct As String
Dim CompCode As String
Dim Plant As String
Dim ProfitCenter As String
Dim PersonResponsible As String
Dim Applicant As String
Dim ResponsibleCostCenter As String
For Each cel In Range("B:B")
If cel.Value = "X" Or cel.Value = "x" Then
MsgBox "Hi"
level = cel.Offset(0, 1).Value
WBSElements = cel.Offset(0, 2).Value
LineText = cel.Offset(0, 3).Value
Type1 = cel.Offset(0, 5).Value
PE = cel.Offset(0, 6).Value
Acct = cel.Offset(0, 7).Value
CompCode = cel.Offset(0, 8).Value
Plant = cel.Offset(0, 9).Value
ProfitCenter = cel.Offset(0, 10).Value
PersonResponsible = cel.Offset(0, 11).Value
Applicant = cel.Offset(0, 12).Value
ResponsibleCostCenter = cel.Offset(0, 13).Value
If Not IsNumeric(level) Or Len(level) > 7 Then
MsgBox "Only numeric values are allowed or Level should be of 7 digits"
level1.Interior.ColorIndex = 3(doesnt work ) needs to change the cell's color
End If
If WBSElements = "" Then
MsgBox "WBS elements cannot be blank"
End If
If LineText = "" Then
MsgBox "Linetext can not be blank"
End If
If Not IsNumeric(Type1) Or Len(Type1) > 7 Then
MsgBox "Only numeric values are allowed or level should be of 7 digits"
End If
If PE <> "X" And PE <> "" And PE <> "x" Then
MsgBox "Not null"
End If
If Acct <> "X" And Acct <> "x" And Acct <> "" Then
MsgBox "Acct should be either X or x or null"
End If
If Len(CompCode) > 4 Then
MsgBox "Invalid compcode"
End If
If Len(Plant) > 4 Then
MsgBox "Invalid Plant"
End If
If ProfitCenter = "" Then
MsgBox "Profit center cannot be blank"
End If
If PersonResponsible = "" Then
MsgBox "Person Responsible cannot be blank"
End If
If Applicant = "" Then
MsgBox "Applicant cannot be blank"
End If
If ResponsibleCostCenter = "" Then
MsgBox "Responsible Cost Center cannot be blank"
End If
End If
Next cel
End Sub
Thanks and Regards
Priya
i.e before the message box is displayed i need to change the color of the cell
Private Sub CommandButton1_Click()
Dim cel As Range
Dim level As String
Dim level1 As Range
Dim WBSElements As String
Dim LineText As String
Dim Type1 As String
Dim PE As String
Dim Acct As String
Dim CompCode As String
Dim Plant As String
Dim ProfitCenter As String
Dim PersonResponsible As String
Dim Applicant As String
Dim ResponsibleCostCenter As String
For Each cel In Range("B:B")
If cel.Value = "X" Or cel.Value = "x" Then
MsgBox "Hi"
level = cel.Offset(0, 1).Value
WBSElements = cel.Offset(0, 2).Value
LineText = cel.Offset(0, 3).Value
Type1 = cel.Offset(0, 5).Value
PE = cel.Offset(0, 6).Value
Acct = cel.Offset(0, 7).Value
CompCode = cel.Offset(0, 8).Value
Plant = cel.Offset(0, 9).Value
ProfitCenter = cel.Offset(0, 10).Value
PersonResponsible = cel.Offset(0, 11).Value
Applicant = cel.Offset(0, 12).Value
ResponsibleCostCenter = cel.Offset(0, 13).Value
If Not IsNumeric(level) Or Len(level) > 7 Then
MsgBox "Only numeric values are allowed or Level should be of 7 digits"
level1.Interior.ColorIndex = 3(doesnt work ) needs to change the cell's color
End If
If WBSElements = "" Then
MsgBox "WBS elements cannot be blank"
End If
If LineText = "" Then
MsgBox "Linetext can not be blank"
End If
If Not IsNumeric(Type1) Or Len(Type1) > 7 Then
MsgBox "Only numeric values are allowed or level should be of 7 digits"
End If
If PE <> "X" And PE <> "" And PE <> "x" Then
MsgBox "Not null"
End If
If Acct <> "X" And Acct <> "x" And Acct <> "" Then
MsgBox "Acct should be either X or x or null"
End If
If Len(CompCode) > 4 Then
MsgBox "Invalid compcode"
End If
If Len(Plant) > 4 Then
MsgBox "Invalid Plant"
End If
If ProfitCenter = "" Then
MsgBox "Profit center cannot be blank"
End If
If PersonResponsible = "" Then
MsgBox "Person Responsible cannot be blank"
End If
If Applicant = "" Then
MsgBox "Applicant cannot be blank"
End If
If ResponsibleCostCenter = "" Then
MsgBox "Responsible Cost Center cannot be blank"
End If
End If
Next cel
End Sub
Thanks and Regards
Priya