Highlight cells in the first column of each sheet based on a list

[rafuk73]

Hi,

I asked one questions on ozgrid forum, but did not receive the reply I wanted.

I basically need one macro which would go to the column A in the sheet Bank_Holidays containing Public Holidays and then highlight in yellow (or any color) the same dates in each worksheet of the workbook. I received a reply to put formulas next to that columns in every sheet which would return truth or false or conditional format.

I do not want to do it as my boss want to have a magic button which would just highlight those dates without going into hassle of extra columns and conditional formatting.

Would be very grateful if somebody could help me

I attached the file and my post in ozgrid is http://www.ozgrid.com/forum/showthread.php?t=196397

Thanks!

[berlan]

Hi,

see how this works:

Please Login or Register  to view this content.

Regards,
berlan

[zbor]

Another approach.

Please Login or Register  to view this content.

Edit: This will remove color when you press button and add if you press again.

[JazzKult]

The same can be achieved by using conditional formatting (but without a helper column). Just name the range with bank holiday dates (in my example 'Bank_holidays') and then create a new CF rule in cell A2 of another sheet using this code:

Please Login or Register  to view this content.

then copy format to the cells below.

[rafuk73]

Thanks, Berlan. Works like a charm!

[rafuk73]

Thanks, zbor! YOu are a star!