VBA cell formatting issues

Utter novice here.

In the process of creating a staff calendar I'm having some issues with a 'summary page' and the colour coding of cells where leave is taken. The staff calendars are formatted so dependent on the type of leave taken, the cell will take a different colour. Trying to keep this as short and simple as possible, here is the VB code for a calendar specifically for "half days" (where my problem arises) e.g. either morning or afternoon off:

Please Login or Register  to view this content.

So as you can see a half day taken either way will take a colour, then read "AM" or "PM". That was a decision taken to neaten the calendars up in order to keep the cell text value looking simple.

That's fair enough for the calendar bit, but I'm having issues with a private summary page to show leave taken across all teams. This summary pulls data from each cell directly, and I'm trying to retain the colour formatting per the main calendar.

The only problem is having copied the VB code above into the summary worksheet, the cells just change to "AM" and "PM" without taking the colour formatting as the calendars do. However to use the first example, if I change the case name from "Ann. AM" to just "AM" it's all fine - which would be okay if I didn't have more than one half day leave type.

I presume I'm missing something very simple and I'll look very stupid, but hey ho...

Many thanks in advance

Let's see:

Could it be an issue with the fact that the summary is pulling the data from the cells which have already had their contents changed (from "Sick. AM" to "AM" e.g.). This would mean that it doesn't know whether it's annual leave or sick leave, just that it's AM or PM.

A suggestion could be to read the ColorIndex of the cell to determine what kind of leave it is when pulling the data?