how can i select the data from 6th row 7th column using VBA

[Bond]

I have 11 columns in my excel sheet.Few fields are mandatory and few are
optional.once the cursor is moved to the next row an error message should be
pointed out,if the mandatory field in the earlier row is missing and those
cells should be highlighted.Can anyone please help me..

 

[Jacob Skaria]

Try the below..Right click the sheet tab>View Code and paste the below code..

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A:K")) Is Nothing Then
If Trim(Target) <> "" Then
Target.Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lngcol As Long
Dim strCol As String, arrCol As Variant
Dim blnBlank As Boolean
strCol = "1,2,3,4,5"
If Target.Row > 1 Then
If Not Application.Intersect(Target, Range("A")) Is Nothing Then
If Target.Interior.ColorIndex <> 6 Then
Application.EnableEvents = False
arrCol = Split(strCol, ",")
For lngcol = 0 To UBound(arrCol)
If Trim(Cells(Target.Row - 1, 0 + arrCol(lngcol))) = "" Then
Cells(Target.Row - 1, 0 + arrCol(lngcol)).Interior.ColorIndex = 6
blnBlank = True
End If
Next
If blnBlank Then MsgBox "Mandatory fields blank"
Application.EnableEvents = True
End If
End If
End If
End Sub

 

[Jacob Skaria]

missed out to mention few points

--Specify the columsn which are mandatory in the string strcol. Currently
1-5 colums are set as mandatory
strCol = "1,2,3,4,5"

--Change the below line to suit

If Not Application.Intersect(Target, Range("A")) Is Nothing Then

to suit your requirement. If it is 1st 11 columns the range should be "A:K"

If this post helps click Yes