How to loop each row if there is data after green colour cell then delete
Category: [Excel] Demo Available
How to loop each row if there is data after green colour cell then delete
Requirement:
The user would like to loop each row if there is data after green colour cell then delete the data till green colour cell.
If there is no data after green colour cell then don't do anything.
If there is no green colour cell then don't do anything.
see sheet3 for the result
The user wants the result in sheet2 - see the file on the following forum:
Solution:
Code:
Sub Delete_It()
Dim Rng As Range, sh As Worksheet, c As Range
Set sh = Sheets("Sheet2")
With sh
Set Rng = .Columns("B:L").SpecialCells(xlCellTypeConstants, 23)
For Each c In Rng.Cells
If c.Interior.ColorIndex = 14 Then
If c.Offset(, 1).Interior.ColorIndex <> 14 Then
If c.Offset(, 1).Value <> "" Then
.Range(.Cells(c.Row, 2), .Cells(c.Row, c.Column)).Delete Shift:=xlToLeft
End If
End If
End If
Next c
End With
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by davesexcel.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to loop each row if there is data after green colour cell then delete |
| How to change cell colour if row contains color |
| How to use VBA code to find colour index which is not found on excel colour palette |
| How to alternate row colours based on text name |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.