VBA and Excel

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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

Board Regular
Joined
Jul 17, 2007
Messages
64
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Where did you put that line?

It should be one line after MsgBox "Hi"
 

priya_anthony

Board Regular
Joined
Jul 17, 2007
Messages
64

ADVERTISEMENT

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

Thanks and Regards
Priya
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top