Microsoft Excel - Shading Cells based on Values on another sheet

Asked By Aaron Stockton on 28-Feb-13 05:44 PM
G'day All,

I have a workbook i'm developing and after a little assistance to automate it.

I have a sheet called 'Resource Request' which has 'E' Column as 'Shift' (Day/Swing/Night) and 'G' and 'H' Columns as Commence and Conclude respectively. I also have 'J' Column as Days of duty (H-G). There is also 'K' which is MO #.

I then have another sheet as 'Resource Allocation - Day' (there is also Swing and Night sheet). These sheets are calendars with 'B2' being the first date and 'C2' being the next etc.

Both sheets have Data Entry rows starting at row '3'.

What I want to do (might be too complex for excel) is if 'Shift' = 'Day' then it goes to the 'Resource Allocation - Day' Sheet and Shades the appropriate cells according to the dates in 'G' & 'H' of the Resource Request sheet.

I am still playing with the idea of having one sheet which displays all Day Night and Swing shifts rather than the 3, will just need to shade the cells different colours.

I would also like to have the text in the Resource Request 'B' & 'C' columns in the shaded which will turn Bold if there is value in the 'K' column of the Resources Request sheet.

Is this possible?

Thank you in advance
Harry Boughen replied to Aaron Stockton on 01-Mar-13 01:19 AM
Hello Aaron,
You will need a macro to do at least the first part.  I would envisage stepping through your column of start dates (ColG) and finding the matching column on the appropriate sheet and then shading the relevant row for the number of columns as determined by the days of duty (ColJ).
The text bolding I think could be done just using conditional formatting based on the contents of cells in ColK.
When I get a moment, I will try to knock together a schema for you.  If you can post a sample workbook that would help a great deal.
Regards
Harry
Harry Boughen replied to Aaron Stockton on 02-Mar-13 10:23 PM
Hello again Aaron,
The attached has a macro that does what I think you want.
shading.zip
The macro will obviously need some changes to match your exact layout and I have used a number of named ranges that you will have to create to match.
Regards
Harry
Aaron Stockton replied to Harry Boughen on 03-Mar-13 05:32 PM
Thanks Harry,  i'll try and manipulate that.
Thank you