Change Index/Match after balance is used

[ElCrow]

I currently have a macro that goes through inserting formulas into cells and a for loop that changes the colors of negative values in the balance column.

I would like to change the "shop floor" value with one from further down in a list when the "Balance" is less than 0.
My thought for this was to create an empty row below the negative to add the value of the next "shop floor" but I am stuck on actually grabbing the next value in a list(the list is stored in a different tab("CCK inv.").

This is my first post so please let me know if you need any more details.

 

[pbornemeier]

Any reason not to use conditional color formatting to color the negative values?

Re: I would like to change the "shop floor" value with one from further down in a list when the "Balance" is less than 0.
How much "further down the list"? What criteria defines what row should be selected?

Re: stuck on actually grabbing the next value in a list
The next different 'Shop Floor" value? The shop floor value in the different tab one row below the same row as this tab? Is that list always sorted the same way?