Issue with conditional formatting in excel

[Ankit_kuchhal]

Hi,

I am facing an issue here with conditional formatting of excel. My cell is changing color on basis of value of another cell.
For e.g. if cell D3='R' then color of cell A1 should change to red
I am using formula in conditional formatting as =D$3="R" then set color as red. I have a macro which insert a row and column. I want that this conditional formatting to get copied too. It is copied successfully and new formula becomes =D$4="R" while i want it to become =E$4="R" as both row and column are inserted. Can anyone help me in this?

Thanks,
Ankit