VBA to copy Conditional Formatting

[AndyJUK]

I have a sheet with a series of tables and other information, I have created the following macro to add a new column to one of the tables and to adjust the formatting of the sheet:-

Sub AddNewWeek()
'

Columns("J:J").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 9.43
Range("K23").Select
Application.Goto Reference:="R23C11:R1000C11"
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
Selection.Cut Destination:=Range("J23:J1000")
Range("L23").Select
Application.Goto Reference:="R23C12:R1000C12"
Selection.Cut Destination:=Range("K23:K1000")
Range("K23:K1000").Select
ActiveWindow.SmallScroll Down:=-22
Range("K5").Select
Selection.AutoFill Destination:=Range("J5:K5"), Type:=xlFillDefault
Range("J5:K5").Select
Range("K17").Select
Selection.AutoFill Destination:=Range("J17:K17"), Type:=xlFillDefault
Range("J17:K17").Select
Range("J18").Select
ActiveCell.FormulaR1C1 = "=SUM(20-R[-1]C)"
Range("A1").Select
End Sub


I also need the macro to copy the conditional formatting rules into the newly inserted cells - at the moment the new cells are just blank.

The conditionally formatted cells are J7:J16 - The macro copies these cells to column K (same row numbers).

The conditional formatting rules are:-

1. Cell Value is Equal to "2" Fill Colour is Green
2. Cell Value is Not Equal to "2" Fill Colour is Red

Is there something I can add so the macro will do this?

Thanks in advance for any help!

Andy

 

[Andrew Poulsom]

You can copy then paste special formats. The macro recorder will give you the code.