Moving formatting with the cell when it moves

[Sartois]

Hi all,

I recently posted about advanced sorting of a column of cells, which Glenn Kennedy kindly solved for me. This is the next request based on the same spreadsheet. As you can see from the attached spreadsheet (and looking at the 'Test Sheet' tab) I've got three active columns. Column A has a series of card names (from a board game), column B has three data validation options, and column D uses an aggregate command (as created by Glenn) that uses a filter so that only those cards with 'Active' in column B are shown there.

What I want to be able to do is to fill certain of the Column D cells in green (for example), so I can easily keep track of the cards I have. However, whenever any of the cards currently Active changes to Discarded or Removed (the other two data validation options), column D changes (as desired), but the cell formatting stays where it is. Is there any way that the formatting can move with the cell? In a perfect world, if a 'green' cell were removed, the formatting would also disappear completely. Any help would be much appreciated.

Thanks,

Sartois

[Pauleyb]

My understanding is that if you manually mark a card with a green background (let's pick 'Blockade') then:
- if you change 'Blockade' to discarded, then the card is removed from column D along with the formatting
- if you add/remove a card that is 'above' arcade in column A (e.g. Asia Scoring) then Column D is appropriately adjusted and Blockade stays green
- if you add/remove a card that is 'below' arcade in column A (e.g. NATO) then Column D is appropriately adjusted and Blockade stays green

As it stands, only the third scenario works, because the Blockade cell did not get moved. Your request could be achieved with a change event macro, but it would be fairly tedious. May I suggest an alternative status of 'ActiveGreen'? You could then tweak the equation in column D to include anything that starts with Active, and then use Conditional formatting to mark any card deemed 'ActiveGreen' to have a green background. Maybe that doesn't scale if you want to have many different types of formatting options, but this would at least keep you out of a macro solution.

[Sartois]

Hi Pauley,

Thanks for your thoughts - they're much appreciated. They've given me a few ideas on how I could broadly achieve the effect I want (and via commands I'm able to understand, heh)!

Thanks again,

Sartois.