Selected Answer
Smhq
On your second question, the attached file has a clickable ActiveX control named "CommandButton1" on the example sheet. That has the commented code below which will loop through uall used rows in column 1 (A) which appears in bold below. When it finds a cell where the next cell is the same value and colour, it will add a blank row and the values from A and B. It also increases by 1 the number of rows to be checked in the Do/ Until loop:
Private Sub CommandButton1_Click()
Dim LstRw As Long, n As Long
' Find last used row (before blanks are inserted)
LstRw = Cells(Rows.Count, 1).End(xlUp).Row
' Loop down used rows
Do
n = n + 1 ' increment row counter
With Cells(n, 1)
' Check if current and next cells are same value and color
If .Value = .Offset(1, 0).Value And .Interior.Color = .Offset(1, 0).Interior.Color Then
'... then insert blank row below and increase last row
.Offset(1, 0).EntireRow.Insert
.Offset(1, 0).EntireRow.ClearFormats
' copy values
.Offset(1, 0).Value = .Value
.Offset(1, 1).Value = .Offset(0, 1).Value
LstRw = LstRw + 1
End If
End With
'quit if (new) last row reached
Loop Until n = LstRw
End Sub
It will work if you have 1.500 used rows in or more in column A.
Regarding your first question, the ActiveX control is also "fixed" using a special event macro which triggers each time there's a change of selection (cell or range) on the worksheet . It just sets the Top and Left proprties of the ActiveX control to the numbers in bold below). That means that is stays where it is even when rows/columns and added or deleted.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Need this for each ActiveX control
With ActiveSheet.OLEObjects("CommandButton1")
.Top = ActiveWindow.VisibleRange.Top + 50
.Left = ActiveWindow.VisibleRange.Left + 100
.Placement = xlFreeFloating
End With
End Sub
Hope this helps.