VBA and Excel

[priya_anthony]

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

 

[jindon]

You declared lvel1 As Range, however you haven't set the refernece for it.

maybe

Set level1 = cell.Offset(,1)
level = level1.Value

 

[priya_anthony]

Hi jindon

I tried doing the same
but it gives an error with saying object required for the below line
Set level1 = cell.Offset(,1)


Thanks and Regards
Priya

 

[jindon]

Where did you put that line?

It should be one line after MsgBox "Hi"

 

[priya_anthony]

Hi Jindon
I have done the same but it still gives an error saying object required
Kindaly help

Thanks and Regards
Priya

 

[jindon]

typo cell -> cel

Set level1 = cel.offst(,1)

 

[priya_anthony]

thanks Jindon
That worked