Highlight Cells based on Cell Reference on another Worksheet

I am trying to find a way to automatically highlight all the cells specified by the cell references in a column on another worksheet (which will constantly have new values added).

On the attached sample, the 'Data Before' tab shows the base data before any highlighting. As cell references are added to the 'References' tab in 'column A' I want the respective cell to be highlighted on the 'Data Before' tab resulting in the 'Data After' tab and each time a new cell reference is added to the 'References' tab, the respective cell is then highlighted on the 'Data Before' tab.

I would like to have control over the highlight colour so that a different highlight colour can be specified for each date change, i.e. all cell references that are shown on the same date are the same colour but where the date changes a different highlight colour can be specified.

I'm really not sure where to start so any help would be welcome.

Many thanks

Maybe:

Please Login or Register  to view this content.

Hi John,

Not sure how this is designed to work, why the randomize? Where should I be adding this code. I've not managed to get this to work as yet?

Many thanks

It would go into the worksheet event module. I may have misunderstood your coloring wants though. Try it on a sample first.

Not suceeded in getting this to work! The colour variations are not an essential. The main point is that any changed cells are highlighted. If the highlights are all the same coloiur that isn't the end of the world, but would just be a good thing to have.

At the moment I've failed to get anything to highlight, so I must be missing something, sorry...

Try this. I took the different coloring out. Test too see if it is what you want. The code is in the event code for Sheet References and works when changes are added in Column A.

Hi John,

Excellent, this seems to work perfectly, I'll test a little more to be sure...

Is there any easy way for the colourindex ref to update automatically based on a change of day. The logic being that if the end user changes the value of a cell on Monday 20th but then also updates the cell vale again on Tuesday 21st with a different value, I need to be able to pick up the fact that a further update has been made (if that makes sense). I could obviously do this manually by editing the colourindex value in the code daily but it would be much neater if this could be automatic. Ideally I think it would be helpful I could specify a select range of colours from the 56 available, maybe in an arra or something, so I can define one for each day of the week and for the code to update based on the day of the week, e.g. Array = (38, 39, 42, 43, 44, 45, 46) to represent Monday thru to Sunday respectively... I just have no idea how to update the code to do this or if it is even possible?

Many thanks

See if this helps. It will vary colors based on the day of the week it is. Todays colorindex is 43. So Sunday-38, Monday-39 and so on.

Hi John,

That is absolutely perfect, thank you so much for taking the time to look at this... simple yet elegant...

Many thanks

You're welcome. Glad to help out and thanks for the feedback.