Copy Conditional Formatting to multiple range

[ngkachun01]

I am currently trying to generate a report from a template now. There are multiple conditional formatting applied on the template and I want to copy and apply the conditional formatting to different columns.
For example,
The conditional formatting were applied on the template Range("A1:A9")
The criteria is that if Range("C1") >1, Range("A1") background will become white.
I wanna apply the same idea, eg. Range("D1") will format depending on Range("F1")'s value, to multiple columns.
I have tried to copy and pastespecial xlpasteformats to others ranges. It works if I copy and paste format column by column.
However, there are a few hundred of columns needed to be applied for CF. Copy and Pastespecial column by column will take very long time.
Instead, I copy the from Range("A1:A9") and paste it to the multiple columns by once, eg Range("D1:D9,G1:G9".....) .
The problem comes now. The CF are not pasted as I wanted.
For Range("D1:D9"), the CF depends on the Range("F1:F9") the first column is alright
For Range("G1:G9"), the CF depends on the Range("F1:F9") the CF for second column should depend on Range("K1:K9") instead.

If there is any way that I can copy and paste the CF in an efficient way.

[acerrhod]

Have you tried using Format Painter (brush icon) - this will copy the CF of the source to target range but take note the the $ (absolute Character) in before and after the Column reference must be remove in CF Formatting i.e. $A$1:$A$9 appears in CF remove those $ = A1:A9 then you can select the source cell the click the Format Painter (brush icon) then apply it to the target range

[ngkachun01]

I have tried using the Format Painter. However, since I was copying from a single column and pasting it to multiple non - consecutive columns, the CF are not pasted as how I want them to be.

[acerrhod]

can you upload sample excel data

[Mondcyte]

Hi there, If I have a conditional formatting using "3 color scale" Format Style, Type "Number" and assigned a value minimum "=$A$1"(GREEN) / Midpoint "percentile=50"(YELLOW) / maximum "=$B$1"(RED) on a cell C1, how can I copy C1 and paste their formats down to C450? If I copy/paste C1 to C2, the reference gets stucked on minimum=$A$1 / Midpoint "percentile=50" / maximum "=$B$1". I tried to remove the dollar sign from it but it gave me an error message " You cannot use relative references in Conditional Formatting criteria for color scales, data bars, and icon sets."

[ngkachun01]

Oh...it has sorted out. Thanks for all the advise.
My macro code actually works. It is just that when I check the CF for the cells which were pasted, such as G1, it shows that it is conditioned on F1. But when I change the value of I1, the format changes. So it works as how I want them to be.
However, I am curious why it still works while the formula in the CF is pointing at a wrong cell.
Sample.xlsm

Attached the Sample file. After running the macro "test". It will insert the data from two other worksheets. I then check the CF for the inserted cell. The formulas in the CF are pointing to the wrong cell. However, the CF are still correct for the inserted cell. I am not sure how it happens.