title: VBA conditional formating with color scale
body: <p>Hi I have this excel table with some data and conditional formatting with these colors. As the months go by, I am adding new columns and rows to the table and would like to have via retain the conditional formatting as the numbers change. I've tried two methods doing the copy and pastespecial pasteformat, and also autofill for the cells. but somehow conditional formatting isn't working, e.g. the color is supposed to be orange when the number is small but my new column/row some how does not follow the rules even when numbers become large. any help is appreciated!</p>
  
  <p><a href="https://i.stack.imgur.com/wm14g.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wm14g.png" alt="enter image description here"></a></p>