excel vba - Modify the pasting of data (constraining the range) -

i using below code:

dim lastrow integer, integer, erow integer      lastrow = activesheet.range("a" & rows.count).end(xlup).row     = 2 lastrow         if cells(i, 2) = "1"              ' opposed selecting cells, copy them directly             range(cells(i, 1), cells(i, 26)).copy              ' opposed "activating" workbook, , selecting sheet, paste cells directly             workbooks("swivel - master - january 2016.xlsm").sheets("swivel")                 erow = .cells(.rows.count, 1).end(xlup).offset(1, 0).row                 .cells(erow, 1).pastespecial xlpasteall             end             application.cutcopymode = false         end if     next 

this works suppose to, need constrain range pastes to. when code run, copies range of a2:z2 (sample range question, copies more rows this), pastes cells beyond column z. concerned column ad there code change text of row green when there value inserted column. after copy/paste code run, row changes green text, though there nothing in ad. here code changes text green in rows (this code in sheet1 object of workbook).

private sub worksheet_change(byval target range) ' dim r range set r = target.entirerow      if target.row = 1 exit sub ' don't change header color      if r.cells(1, "ad").value <> ""         r.font.color = rgb(0, 176, 80)     else         r.font.colorindex = 1     end if end sub 

now, in column ad of row complete work on, insert date , time in format: 1/4/2016 13:20. can change line:

if r.cells(1, "ad").value <> "" 

to check format rather value?

i still learning vba, know have lot learn. assistance appreciated.

edit: anomaly not occur until after code run (which located in target workbook "swivel"):

sub remove_duplicates() ' application.screenupdating = false      activesheet.range("$a$1:$z$2000").removeduplicates columns:=array(10, 11, 12, 13, 14, 15, 16), header:=xlyes     activewindow.smallscroll down:=6  range("c" & rows.count).end(xlup).offset(1).select  application.screenupdating = true  end sub 

this changes text green color, there nothing in column ad of row trigger change.

since question see asked here is:

can change line: if r.cells(1, "ad").value <> "" then

to check format rather value?

here 1 way it:

me.cells(target.row,30).activate 'column 30 ad if application.executeexcel4macro("get.cell(7)") = "m/d/yyyy h:mm;@" 

adjust formatting exactly needed. guessed based on information in question.