vba - Format excel cells based on other cells range -

i have table many rows , columns.

i need format range of cells in column based range made 3 cells

for instance let's have value of 100 in a1, have 2 limits, have lower limit (-3) in cell b1 , upper limit (4) in c1

then select range of cells want change color , apply "format cells contains" rule, first use "between =b1+c1 , = b1 + c1" - color green "not between =b1+c1 , = b1 + c1" - color red

the problem appears when want copy rows below, if did not block cell, can't manage copy formatting rows below, tried paste special options, values remain related a1, b1 , c1, don't change a2, b2, c2 , on.

any idea on i'm doing wrong?

maybe should use macro it, don't know vba guide can manage.

thank you.

edit: thank code,

i want edit need start row 3, , take in account c + e low value , c + d upper value, change color cells in range g v.

i tried edit code, i'm not sure how so, below tried modify = 3 lastrow

if ws1.cells(i, "g:v") >= ws1.cells(i, "c" + "e") , ws1.cells(i, "d:v") <= ws1.cells(i, "c" + "d")     ws1.cells(i, "a").interior.color = rgb(0, 255, 0) 'if value in column in betwen (color green) else     ws1.cells(i, "a").interior.color = rgb(255, 0, 0) 'if value in column not in betwen (color red) end if 

you can vba:

option explicit sub test()  dim ws1 worksheet dim lastrow long dim long  set ws1 = thisworkbook.sheets("sheet1") 'change name of sheet  lastrow = ws1.cells(rows.count, 1).end(xlup).row  = 1 lastrow      if ws1.cells(i, "a") > ws1.cells(i, "b") , ws1.cells(i, "a") < ws1.cells(i, "c")         ws1.cells(i, "a").interior.color = rgb(0, 255, 0) 'if value in column in betwen (color green)     else         ws1.cells(i, "a").interior.color = rgb(255, 0, 0) 'if value in column not in betwen (color red)     end if  next  end sub 

edit:

sub test()  dim ws1 worksheet dim lastrow long dim long  set ws1 = thisworkbook.sheets("sheet1") 'change name of sheet  lastrow = ws1.cells(rows.count, 1).end(xlup).row  = 3 lastrow ' start @ row 3  lowerlimit = ws1.cells(i, "c") + ws1.cells(i, "e") ' low value upperlimt = ws1.cells(i, "c") + ws1.cells(i, "d") ' upper value      c = 7 22 ' column g column v              if ws1.cells(i, c) >= lowerlimit , ws1.cells(i, c) <= upperlimt                 ws1.cells(i, c).interior.color = rgb(0, 255, 0) 'if value in betwen (color green)             else                 ws1.cells(i, c).interior.color = rgb(255, 0, 0) 'if value not in betwen (color red)             end if      next c  next  end sub