Using INDIRECT in a macro/vba
 

I have set up a workbook to show the manning schedule for where i work. Each
sheet represents each week of the year and the layout is the same for each,
with days of the week and what each person is working, holidays, sick etc,
with calculations to show hours and costs involved. I have an extra sheet
which i have set up to show 2 weeks on one page, but only shows the days of
the week minus any calculations. Currently i have set this up, so it looks at
today's date and from that works out the current week and the following week,
and then by using an INDIRECT formula to look up the date and pull the
relevant information from the correct weekly sheet. I have got this part
working ok, however as we change the colour of a cell to show a holiday or
sick day, it doesn't show this formatting on the 2 week summary sheet. Is it
possible for the cell formatting to also be shown if the INDIRECT function
was used in a macro instead ? I have been trying to find examples of this,
but so far been unsuccessful. Any help would be greatly appreciated.