VBA Coding - how to force all cells in a row to be completed

[DonumDei]

Hi All,

I having trouble getting this to work fully . The issue I have is that I need to force cells E2:J2 to be completed in order if cell D2 is populated, and to remove any cells that are not completed in order. With this repeating for each row of my spreadsheet.

I have a current code that doesn't work fully , It forces them to fill in the cells in this range however gets confused with different rows.

E.G

Row 2 is fully filled in ( No Issue )
Row 3 is filled in up to c2 ( No issue)
Row 4 is then bringing up an error when filling in D2 and onwards that all the rows have not been completed - ( meaning the row above )

The code I'm using is as follows

[COLOR=#000000][FONT=Courier New]Private[/FONT][FONT=Courier New]Sub[/FONT][FONT=Courier New]Worksheet_Change(ByVal[/FONT][FONT=Courier New]Target As[/FONT][FONT=Courier New]Range)[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]    If[/FONT][FONT=Courier New]Target.Count <> 1 Then[/FONT][FONT=Courier New]Exit[/FONT][FONT=Courier New]Sub[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]    Dim[/FONT][FONT=Courier New]isect As[/FONT][FONT=Courier New]Range[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]    On[/FONT][FONT=Courier New]Error[/FONT][FONT=Courier New]Resume[/FONT][FONT=Courier New]Next[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]    Set[/FONT][FONT=Courier New]isect = Application.Intersect(Target,Range("D2:J1000"))[/FONT][/COLOR]

[COLOR=#000000][FONT=Courier New]    If[/FONT][FONT=Courier New]Not[/FONT][FONT=Courier New](isectIs[/FONT][FONT=Courier New]Nothing) Then[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]        If[/FONT][FONT=Courier New]Target.Column = 1 Then[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]            If[/FONT][FONT=Courier New]Len(Target.Value) > 0 And[/FONT][FONT=Courier New]Len(Target.Offset(-1, 0).Value) = 0 Then[/FONT][/COLOR]
[FONT=Courier New][COLOR=#000000]                MsgBox"", vbInformation,[/COLOR][/FONT]
[FONT=Courier New][COLOR=#000000]                Target.ClearContents[/COLOR][/FONT]
[COLOR=#000000][FONT=Courier New]            End[/FONT][FONT=Courier New]If[/FONT][/COLOR]
[FONT=Courier New][COLOR=#000000]        Else[/COLOR][/FONT]
[COLOR=#000000][FONT=Courier New]            If[/FONT][FONT=Courier New](Len(Target.Value) > 0 And[/FONT][FONT=Courier New]Len(Target.Offset(-1, 0).Value) = 0) Or[/FONT][FONT=Courier New](Len(Target.Value) > 0 And[/FONT][FONT=Courier New]Len(Target.Offset(0, -1).Value) = 0) Then[/FONT][/COLOR]
[FONT=Courier New][COLOR=#000000]                MsgBox"", vbInformation, [/COLOR][/FONT]
[FONT=Courier New][COLOR=#000000]                Target.ClearContents[/COLOR][/FONT]
[COLOR=#000000][FONT=Courier New]            End[/FONT][FONT=Courier New]If[/FONT][/COLOR]

[COLOR=#000000][FONT=Courier New]        End[/FONT][FONT=Courier New]If[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]    End[/FONT][FONT=Courier New]If[/FONT][/COLOR]
[COLOR=#000000][FONT=Courier New]End[/FONT][FONT=Courier New]Sub[/FONT][/COLOR]

Any Help will be appreciated

Thanks

 

[offthelip]

I haven't got the time to write the code, but the way I would do is using two while loops one for columns and one for rows starting at the target row and column and counting backwards to D2, and check each cell to see if it is blank , if you find a blank cell you clear the contents. To speed it up i would load the whole range into a variant array to do the checking.