Conditional Format & Vlookup Error!

Hi,

I am trying to create a Holiday Planner for my company. After extensive research and learning i have gotten so close to finishing but I am getting an error which means the dates i input are not formatting the requested cells...please see attached.

Info:
JAN JUN Cell I10 was the first formula with conditional formatting...I dragged this across but i get error messages and it wont colour the cells red as i am trying to request.

Dates are taken from Sheet EmployeeData Cells BS4 to BU603. The names are repeated 10 times down this list as people may take more than 1 slot of time in the year...I hope you can make sense of it. I basically did this as I thought it was the only way VLOOKUP would work and now I'm thinking because ive repeated names this is where it is hanging up?

I know i could have used VB but I am still trying to get my head around the basics, so I tried!

Idon't know if this will be a problem but i did creat a macro for a calendar which i short-cut as CTRL-Z (I now have learnt this is undo)

Thank you in advance to any help or ideas you can give!

Regards

Warren

Hello,

do you know the difference between absolute and relative cell references?

=A1 copied down will become A2. Copied across will become B1
=$A1 copied down will become $A2. Copied across will stay $A1
=A$1 copied down will stay A$1. Copied across will become B$1
=$A$1 will stay $A$1, wherever it is copied.

This should help you with the changing cell references.

cheers

If you want to anchor the lookup array, use the '$' to make the addresses absolute:
I don't know what the D10 cell represents or if/how it should change when dragging the formula across the row.

Please Login or Register  to view this content.

This solution requires an additional sheet (cal) and the formula:

Please Login or Register  to view this content.

The Cal sheet assumes that Sunday is day one of the week and so, does not count days six and seven(Friday,Saturday)

The first ADDRESS fn finds the top of the search range, the second, the bottom

Please post your latest workbook.

Fugro,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Hi everyone,

I managed to find out the reason my form was not working was due to the fact that i had protected the sheet. I would post my latest work but it is up to 4MB...this is why i need to learn VB!

I just want to thank everyone for their help, really is much appreciated that you give your time so freely to helping others!

Next time I will code/uncode the text, thanks teyln

I now need to close this as Solved!?

Best Regards

Warren