Duplicate Cell contents from here to there, when another cell changes. Using VBA

[Allieanne]

I need to figure out how to move a data from one row to another using VBA

In attached spreadsheet, Cell D2 in yellow. If you type in the number 1, it triggers a vlookup in Cell E5 to bring in a definition (From Sheet2). I then want to use VBA to copy that definition immediately from E5 to E2. It needs to be copied over but I can't use E5=E2 as E2 will then be edited to specific circumstances for that particular job.

I think its an intersect.target function. In my little sample, the Grade 1 and Grade 2 cells are part of named range "GRADES". There will be 10 of these Grades cells in actual workbook.

So, I want a function that

- if any cell in the named range "GRADES" changes, put the vlookup definition for that particular grade into the appropriate definition cell.



I managed to do this when my data went down the columns rather than across the row, but I need to make it work across the row this time.