Generating List of Week Working days and Identifying Holidays

From another thread I got this formula placed on B5:
=NETWORKDAYS(EOMONTH(B4,-1)+1,EOMONTH(B4,0),Holidays)

his generates the number of working days for that month (regardless of the date on B4) that exclude Holidays listed under the range "Holidays".

I also got this code (Credits to XXXX)

Please Login or Register  to view this content.

That will generate the number of week days. However, this is my issue, and those that understand the code will notice, that regardless of the date I input on B4, say May 5, 2009, but as I enter something on column C, it will change my B4 date to Oct 28 and give me all week days for October only. I will then change B4 to say June 5, 2009, and delete the dates generated by the code and input something on Column c again, and again, it will change B4 to Oct 28,2009.

Any ideas on how tell the code not to change my date, and generate the week days for the month of the date on B4 instead. And then naturally, if there is a holiday in that range generated, it will list the day as well (i.e. it will not be excluded). This is fine, however, can anybody help me to put beside the holiday date (listed under range Holiday) on the range generated by code the word "Holiday", and beside all others "Working Day"?

Thank you.

Ron
Excel2003

EDIT

I have deleted this line:
.Range("B4").Value = Date

Now it doesn't change the date on B4, but the list of week days generated is still for October!! :-(

I guess I / we're confused as to what it is you want to do...

I'm guessing you want to use VBA to generate for you the working days of the month specified by date in B4 ?
And presumably you want to do this without using the NETWORKDAYS function (ie avoiding Analysis ToolPak), correct ?

Rather than use a Worksheet_Change event why not use a UDF - it will make it easier to use in other cells / sheets etc ?

eg

Please Login or Register  to view this content.

The above, stored in a Module can be called from any cell along the lines of:

=NWDAYSMONTH(B4,Holidays)

If you still want to run as part of a Change event you can do of course you just to modify re: inputs.

Not really tested but perhaps replace your existing code with

Please Login or Register  to view this content.