How hard is it to do this?

[Sheltered]

Hi, not sure how difficult this is as im no expert on Excel.
I have 2 spreadsheets one with details of holidays, ie start date, end date, number of hours taken etc, and another spreadsheet set up as a rota with all dates across the top and staff down the left side.
What i want is to be able to enter a date in the holiday spreadsheet and have the corresponding date on the rota spreadsheet change to a 'H' and turn blue.

ie. i enter 09/12/2003 as a holiday and the cell in the rota spreadsheet changes as below, but colour aswell.


BEFORE:
* |09/12/2003|10/12/2003|
PB| D | D |

AFTER:
* |09/12/2003|10/12/2003|
PB| H | D |


Hope that makes sense. Any ideas where to start with this?

Thanks very much

Pete

 

[DreamerZ]

In the Worksheet_Change() event, you could add code to store the entered date:

Dim dtHoliday as Date
dtHoliday=ActiveCell.value

open the rota workbook:

Workbooks.Open "rota.xls"

find the associated date entered

Set fndFind = .Find(dtHoliday)
If fndFind Is Nothing Then
MsgBox "Date Not found"
Else
Range("").Value="H"
Range("").Font.ColorIndex=(blue)

The Range("&quot entry would be set to select the cells you want to add the blue H to. If you want to do it for all personnel, the enter that whole range.

You could do the above on a button (click the button with the holiday date cell active) or set the Application.MoveAfterReturn to false so that the active cell doesn't change after entering the date. I'd probably put it on a button to eliminate any possible problems with other cell changes.

Hope that helps.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[SkipVought]

Hi,

1. The color can be formatted using Format/Conditional Formatting using this formula as Formula Is

=MATCH(B$1,Holidays,0)>0

where the range of holiday dates has been given a Range Name of Holidays.

2. Use this forumla to change the Value in the cells

=IF(ISERROR(MATCH(B$1,Holidays,0)>0),"D","H")

Skip,

http://www.TheOfficeExperts.com

 

[Sheltered]

Thanks guys, seems straight forward enough. ill have a play around and see what i can do.

Pete