VBA - Input Value If Column Number Equals "x" within Non-Contiguous Named Range

Hi all,

First post as a newbie to VBA and Excel - have found these forums greatly useful in the past and am severely at a loss for how to accomplish the task at hand. Essentially, I want to create a macro that will auto-populate a predefined non-contiguous range of cells with a value that is based on another cell's value. The value that is populated depends on the cell's column number, which corresponds to a user inputted table.

I've attached a sample worksheet (the yellow cells are the predefined non-contiguous range of cells) - I do need a VBA solution, rather than formulas within the cells, because part of my macro will determine a unique placement of the non-contiguous range.



I know I can use a named range to address the non-contiguous range of cells in my macro. What I think might work is to identify the column number for each cell within the named range and insert the corresponding value in the user input table (perhaps using offsets?) I just don't know where to start - any help would be greatly appreciated!