When you might need to apply color sales Conditional Formatting to multiple rows in Excel using a Macro?  We found that is most useful, when you have a perfectly arrange row-by-row conditional formatting, and you are trying to insert a new row.  You can try to fix the resulting formatting rules mess manually, or use this macro to set all formats in the range.

First you n first row need to select the range were your conditional formatting will apply and clear all formatting rules.

Next, you need to setup proper color scale conditional formatting in the first row of the range.

Now, you should create a name range to reference in a macro.  In this example, the name range is called “SPX_Price_Range”, but you can adjust this name to your needs.

Finally create a macro similar to the macro below, and run it.

Sub PasteFormat()

' Select
Range("SPX_Price_Range").Select
Selection.Copy

i = 0
Do While i < 140
' Move One row down
ActiveCell.Offset(1, 0).Range("A1").Select
'Check if row has a valid value
If ActiveCell.Value = "X" Then
Exit Do
' Paste Formats Only
Else
Selection.PasteSpecial Paste:=xlPasteFormats
End If
i = i + 1
Loop
End Sub

What this macro does?  It copies a content of a name range “SPX_Price_Range” and try to paste formats only down to 140 rows or until value of the first cell in the next row is not “X”.

This macro can be improved b:
– checking the existence of a name range;
– removing hard-coding (140 rows).