View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
OssieMac OssieMac is offline
external usenet poster
 
Posts: 2,510
Default Need help with merging a range of cells

My apologies. I made some changs to the program after initial testing and
then did not look at the results carefully enough after making those changes.
Replace it with the following code.

Sub RemoveSuperfluous()
Dim rngUnit As Range
Dim c As Range

With Sheets("Sheet1")
Set rngUnit = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

For Each c In rngUnit
If c = c.Offset(1, 0) Then
'Set interior color for both Unit# and Team Leader
Range(c.Offset(1, 0), c.Offset(1, 1)).Interior.ColorIndex = 6
End If
Next c

'Stop 'Stop here if you want to test what is being cleared

For Each c In rngUnit
If c.Interior.ColorIndex = 6 Then
'Clear contents only for both Unit# and Team Leader
Range(c, c.Offset(0, 1)).ClearContents

'Remove ColorIndex for both Unit# and Team Leader
Range(c, c.Offset(0, 1)).Interior.ColorIndex = xlNone
End If
Next c

End Sub

--
Regards,

OssieMac