PLEASE HELP!!! Loop through worksheets 2 - 26 and find blanks in columns 1, 2, 3, 4, 5, 6 with varying rows

[brandonslee]

Hi.
I'm quite a newbie w/ Excel VBA and need your help. I've struggled with this for quite a while and can't seem to figure it out.
I need a VBA code that runs through each row in columns 1 thru 6 and finds blank cells in columns 2 to 6 when there's a value for column 1 in the same row. I need to have this loop from worksheet 2 thru 26 with each having varying row counts.


Sub Check_Null()
'
' Check_Null Macro
'


'
Application.Goto Reference:="Check_Null"


Dim i As Long
Dim emptyA As Integer
Dim emptyB As Integer
Dim emptyC As Integer
Dim emptyD As Integer
Dim emptyE As Integer
Dim emptyF As Integer
Dim w As Integer
emptyB = 0
emptyC = 0
emptyD = 0
emptyE = 0
emptyF = 0


'2 to 26
For w = 2 To 3


For i = 1 To Rows.Count

If IsEmpty(Worksheets(w).Cells(i, 1).Value) Then
Worksheets(w).Cells(i, 1).Interior.ColorIndex = 6 And emptyA = emptyA + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 2).Value) Then
Worksheets(w).Cells(i, 2).Interior.ColorIndex = 6 And emptyB = emptyB + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 3).Value) Then
Worksheets(w).Cells(i, 3).Interior.ColorIndex = 6 And emptyC = emptyC + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 4).Value) Then
Worksheets(w).Cells(i, 4).Interior.ColorIndex = 6 And emptyD = emptyD + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 5).Value) Then
Worksheets(w).Cells(i, 5).Interior.ColorIndex = 6 And emptyE = emptyE + 1

ElseIf Not IsEmpty(Worksheets(w).Cells(i, 1).Value) And IsEmpty(Cells(i, 6).Value) Then
Worksheets(w).Cells(i, 6).Interior.ColorIndex = 6 And emptyF = emptyF + 1

End If

Next i

Next w


If emptyA > 0 Then
'MsgBox "You have a blank value in Column_1. Please check the highlighted rows in the Column_1"


ElseIf emptyB > 0 Then
'MsgBox "You have a blank value in Column_2."


ElseIf emptyC > 0 Then
'MsgBox "You have a blank value in Column_3."


ElseIf emptyD > 0 Then
'MsgBox "You have a blank value in Column_4."


ElseIf emptyE > 0 Then
'MsgBox "You have a blank value in Column_5."


ElseIf emptyF > 0 Then
'MsgBox "You have a blank value in Column_6."


End If


End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

I need a VBA code that runs through each row in columns 1 thru 6 and finds blank cells in columns 2 to 6 when there's a value for column 1 in the same row.

Keep telling us in words what you want to do once you find these cells rather than have us trying to guess from your code that apparently doesn't work how you want anyway.