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?
Thanks in advance!
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?
VBA Code:
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!