Date function in VBA

[mcinnes01]

Hi,

I am trying to write some VBA, which I am quite new to, to basically insert an image or shade the background, in the graphical summary table I've produced for the date ranges from each persons holiday tab.

I also want some master shading in the table which shades the weekend in orange. This has to be in VBA as the table is dynamic and updates based on the year drop down off my holiday formulas tab.

The image i'd like to insert is the bucket and spade in the top left corner of the summary tab.

I've got the script set up in VBA that I want the structure to fall in to, but I am struggling to work out a logic for looking at the holiday date ranges of each persons tab and then excluding weekends for the entry of a symbol or the interior.colorindex being set.

P.S. password is: payrollhols

[royUK]

You can use Conditional formatting to do this, use the WEEKDAY Function in the Formula

[royUK]

I've changed one block, note the formulas to display the date, not using Text

[mcinnes01]

How would I do this in regards to shading the column for saturday and sunday for each month?

Would you set conditional seperately for each day of the week in each month and tell it to look at the day of the week for that day of the month?

Would it not be quicker to it in VBA as, if the above is correct that would mean there is 365 columns that need to be individually conditionally formated? And how would i get the formula to look at the dates people are away off their tab and format this? would me each cell would need its own separate conditional formating? meaning i would need to format 3285 individual cells? Or can this be done for each month meaning 12 conditional formating ranges?

Also as this holiday chart is dynamic, when the summary tab is deactivated the VBA script clears the formating, would standard conditional formating work in this case?

In a previous project I found it easier to do it on activate, that way it updates everytime you click on the tab.

[mcinnes01]

yes that seems to work for the weekday, I was worrid the VBA would over write this when the tab closed and then the standard conditional formatting would not rewrite afterwards.

I'm still clueless as how to lookup the dates for each person and format them in to the table.

Can you insert an image with conditional formating?

[royUK]

There's no point using VBA if Excel has inbuilt functions available. Once you've edited the sheets to match what I have done it will be dynamic for any date changes

I don't understand the code in your summary sheet is for

[mcinnes01]

That is what I wrote for something similar that did a function the other way round e.g. the days of the week were static headers and the calendar populate the day of the month starting with the for example the 1st under monday or the 1st under wednesday and then filled out the days of the month automatically for what ever year I selected.

The code came in as I need 6 variables to conditionally format by so VBA was required. What is in the summary sheet is everything from my other project less what didn't relate to this sheet and thats why it doesn't make sense as there are no variables set.

I have attached my other project so you can see what I mean.

[royUK]

This will run more efficiently.

Please Login or Register  to view this content.

I'm not really sure what it's for. I know it colors the cells, but can you explain the reasoning

[mcinnes01]

Does that screen updating stop it do the thing where it runs through the cells?


Any way, basically as I thought the principle was more or less the same e.g.

a table with dates that need color coding on a graphical table

I thought I would use the same structure and just adapt it to my project, but I am having difficulties in that there are date ranges instead on individual dates and that rather than it being just 1 calendar it is essential 9 calendars in a list.

[mcinnes01]

Oh, and in regards to your last question, the colourful payroll calendar is essential for giving to the payroll team and, in future when we start providing payroll solutions, to other businesses. It means we can run off a calendar that has the cutoff date for submitting various data to run off the payroll and it makes it simple for them to use. I think we are getting our marketing team to produce some desk stationary using it, like those triangular cardboard calenders you get etc.

[royUK]

I've stopped the cells being activated which is the main saving in speed, the screenupdating also helps a little.

What I'm not understanding is the reasoniong behind the comparisons.

When I get home I'll look for some similar examples

[mcinnes01]

Okay thanks, essentially for each person on the payroll hols workbook i need their holiday days to be shaded on the summary table in their coresponding line. The problem I can't get my head around is doing this for each person.

In the past example there were 6 variables and 12 ranges,
However in this example there is 1 variable, 12 ranges but then 9 people.

In VBA terms this meant previously I had 72 cases to set,
Whereas this new project has 108 cases to set.

There is also an additional problem of multiple date ranges for each criteria and the fact i need to exclude weekends from the ranges in terms of formating.

In terms of the multiple date ranges, thinking about it, could I compose them in one string, and precurse that with a weekday function.

E.g. Andy

Weekday(Range(Worksheets("Andy").Range("B14:C14,B15:C15,B16:C16,B17:C17,B18:C18,B19:C19,B20:C20,B21:C21,B22:C22,B23:C23")

[mcinnes01]

Please Login or Register  to view this content.

This is kind of what I was thinking, using andy as an example, it doesn't work and doesn't include the weekday function but hopefully will give you a better picture as to what I am trying to achieve.

Thanks,

Andy

[mcinnes01]

I can see why that doesn't work but don't know how to fix it.

It currently looks at a blank range on the Summary tab where as it needs to look at the line with the date in it.

It then needs to color format the cells that relate to andy.

How can i get it to look at the dates e.g. b3:co3,b15:co15,b27:co27,b39:co39?

[shg]

mcinnes, please edit your post to add CODE tags.

[mcinnes01]

I've tried to add a range to the following cases but i get a debug message, I am trying to set the from and to dates on Andys tab as my case range. So that those date are color formated on the summary tab.

Please Login or Register  to view this content.