Holiday Calendar Problem

Originally Posted by paulwelburn

I have devised a holiday calendar for employees (see attached). All works fine on the individual employee worksheets using the VB code to give each category a colour ie BH will turn out an orange colour.The problem occurs with the summary sheet.It takes the data from the employee worksheets and uses the same VB coding to change the categories to the appropriate colour.The data the summary receives is correct but the colours won't activate until I double click the cell.

Is there something I'm missing?

Hi,

missing a couple of things.

For the individual worksheets you are using Worksheet_Change to trigger the colour, if you use this in the summary you will also need to 'change' (ie Select and Enter) the cell.

Perhaps the easy way is for the Worksheet to also colour the Summary.

One point on your formula, If you set Summary A column with the names shown on the sheet then you could use a single formula for every row and column, instead of having to type and retype and retype the persons name.

hth
---

added

for C4 try (with A4 set to the SheetName),

=IF(INDIRECT("'"&$A4&"'!B4")="","",INDIRECT("'"&$A4&"'!B4"))

and D4

=IF(INDIRECT("'"&$A4&"'!C4")="","",INDIRECT("'"&$A4&"'!C4"))

and formula fill these downwards etc

Better would have been to align the columns, set and hide a column B on the individual's worksheets so that column C shows C results, etc.

---