VBA to immediately change the colour of a cell depending on the code placed in another cel

Hello,

Firstly please let me advise that standard conditional formatting will not work here as this requires ten conditions to be set.

I have a worksheet that contains 40 'paired' columns. In the first column of each pair I can enter any one of ten condition codes. The corresponding cell in the second of the paired columns needs to change its interior colour to that dictated by the code in the first of the pair.

I have tried a few ways without success with my last attempt almost making it where I used three named ranges and this code:

Please Login or Register  to view this content.

Unfortunately this hit the limit on the number of separate columns I could place within one named range and the macro only ran through 4 of the paired columns - even if this had been successful it would still have been unsatisfactory as a macro had to be run to refresh the cells colours. I wish to have the colour change as the code is changed.

I hope someone in this forum has had a similar requirement in the past and can provide a solution.

I am using MS Excel 2003 SR3.

Thanks in anticipation

Hello Joseph,
Please see attached.Paired Columns Worksheet.xls
Thank you.
Phil

Hi Phil.
I don't get what you're after (conditions2use ??? - data2use ???) but would, like JosephP said, a worksheet_change event with Select Case work?

Hello Joseph,

Excellent you gave me what I needed.

Thank you very much.

Phil