Copying row with Conditional Formatting to one or more rows

[Hamish Drewry]

I have created a spreadsheet with different conditional formatting set up for each column in a row.

I am able to copy this "source" row to another row - the cell references within the formula used in conditional formatting are correctly updated. Furthermore, the range the conditional formatting in each cell applies to is restricted to the corresponding cell in the new row.

However, when I copy the same "source row" to more than 1 row, the the cell references remain unchanged and point at the cells in the source row. Also, the range the conditional formatting in each cell applies to is extended to the corresponding cells in all the newly copied rows.

I do not want to have to copy/paste one row at a time as we may be dealing with hundreds of rows.

Any suggestions/ideas please?

Thanks

Hamish

 

[Hamish Drewry]

Inspired by the following thread: https://www.mrexcel.com/forum/excel...y-move-rows-highlighted-based-conditions.html

I have sorted the problem using the following:

Sub Copy_Rows()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim c As Range
Dim MyValue As Integer
Dim strValue As String


strValue = InputBox("Enter Number of Rows to Add")


If Not IsNumeric(strValue) Then
' Invalid parameter passed
Exit Sub
End If


If CDbl(strValue) <> Round(CDbl(strValue)) Then
Exit Sub
End If

MyValue = CInt(strValue)


Lastrow = Sheets("Main Input Tab").Cells(Rows.count, "A").End(xlUp).Row
Sheets("Main Input Tab").Activate
For i = 1 To MyValue
For Each c In Range("A" & Lastrow & ":T" & Lastrow)
Rows(Lastrow).Copy Destination:=Sheets("Main Input Tab").Rows(Lastrow + i)
Next
Next
Application.ScreenUpdating = True
End Sub