Paste Formatting offset

[karkas]

Hello,

I am trying to paste some conditional formatting to many, many non-contiguous rows of cells, but do run in groups of 7-20. Ultimately, I wanted to simply select all the cells I want to paste the formatting to, but the conditional formatting result doesn't work out right, because it is comparing the cell on the left to the cell on the right and highlighting the greater value one. If I select them all together it only applys for the entire selection.... I have to paste 1 row at a time to get around that, but with so many rows it will take forever. To reduce most of the workload, I tried to write VBA to paste 10 rows at a time, but I am not setting it up right.

In the code below, it will paste correctly in the active cell & the second row (i = 1), but since it is changing active cell focus on each one, as it loops through the iterations, it skips rows by each i with the final i=10, pasting 10 rows below the final selection and skipping all the rows in-between in the process. How do I fix this so it will just move down one cell each time? Alternatively, is there a way to simply select all the cells I want the conditional formatting in each highlighted cell and its comparitor adjascent cell all at once?

Sub Paste_Conditional_Formatting()
    Dim i As Integer
    
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteFormats

    For i = 1 To 10 'loops through 10 rows
    ActiveCell.Offset(i, 0).PasteSpecial Paste:=xlPasteFormats 'pastes conditional formatting
    Next i
End Sub

Thanks in advance!

 

[rlv01]

How about this?

Sub Paste_Conditional_Formatting()
    ActiveCell.Resize(10).PasteSpecial Paste:=xlPasteFormats  'pastes conditional formatting
End Sub

 

[karkas]

Thanks for the try @rlv01, but that didn't work.

If I run that, it will select the next 10 rows and apply the conditional formatting across those 10 cells.
Example:
1) I manually select A1
2) run code
3) A1:A10 is selected and the conditional formatting highlights the single highest value of those 10 cells.

I need it to:
1) I manually select A1
2) run code
3)
A1:B1 conditional formatting highlights the cell with the higher value.
A2:B2 conditional formatting highlights the cell with the higher value.
A3:B3 conditional formatting highlights the cell with the higher value.
A4:B4 conditional formatting highlights the cell with the higher value.
A5:B5 conditional formatting highlights the cell with the higher value.
A6:B6 conditional formatting highlights the cell with the higher value.
A7:B7 conditional formatting highlights the cell with the higher value.
A8:B8 conditional formatting highlights the cell with the higher value.
A9:B9 conditional formatting highlights the cell with the higher value.
A10:B10 conditional formatting highlights the cell with the higher value.

 

[rlv01]

I need it to:
1) I manually select A1
2) run code
3)
A1:B1 conditional formatting highlights the cell with the higher value.
A2:B2 conditional formatting highlights the cell with the higher value.
A3:B3 conditional formatting highlights the cell with the higher value.
A4:B4 conditional formatting highlights the cell with the higher value.
A5:B5 conditional formatting highlights the cell with the higher value.
A6:B6 conditional formatting highlights the cell with the higher value.
A7:B7 conditional formatting highlights the cell with the higher value.
A8:B8 conditional formatting highlights the cell with the higher value.
A9:B9 conditional formatting highlights the cell with the higher value.
A10:B10 conditional formatting highlights the cell with the higher value.

This will do that w/o copy paste.

Sub FormatTenRowsFromActiveCell()
    Dim FormulaString As String

    With ActiveCell
        FormulaString = "=" & .Address(False, True) & ">" & .Offset(0, 1).Address(False, True) 'i.e. =$A1>$B1
    End With

    With ActiveCell.Resize(10)
        .FormatConditions.Delete
        With .FormatConditions.Add(Type:=xlExpression, Formula1:=FormulaString)
            .SetFirstPriority
            With .Interior
                .Color = vbYellow
            End With
        End With
    End With

    With ActiveCell
        FormulaString = "=" & .Address(False, True) & "<" & .Offset(0, 1).Address(False, True) 'i.e. =$A1<$B1
    End With
    With ActiveCell.Offset(0, 1).Resize(10)
        .FormatConditions.Delete
        With .FormatConditions.Add(Type:=xlExpression, Formula1:=FormulaString)
            .SetFirstPriority
            With .Interior
                .Color = vbYellow
            End With
        End With
    End With
End Sub

 

[karkas]

That worked perfectly. Thanks so much!