Solved: Drag Over Event for Cell Color Format Properties

[imatte]

I want to be able to click and drag over cells and have them change color and if they already have color then remove the color. This is for a schedule template that I downloaded and modified. You should be able to choose the empployee with a drop down, and each employee should be a different color, click and drag over the cells that represent start and end times and then total the hours at the end.

Any help would be greatly appreciated.

[imatte]

I am new to this forum and I am trying to upload the template

[imatte]

hopefully this post will have the file attatched

[Bob Phillips]

I think you need 5 posts before you can upload, so just post a couple of bump messages.

[mdmackillop]

Hi imatte,
You can post only certain types/size of files. You'll see them listed in Manage Attachments in the Go Advanced section

[imatte]

I think you need 5 posts before you can upload, so just post a couple of bump messages.
Thanks XLD I will try that

[imatte]

And basically what I want to do is click in a cell and have a specified color toggle on and off... also I would like the same result if you drag over a cell with the mouse button held down

[mdmackillop]

Try the following. Insert a ColorIndex number in column D of Employee List
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyColour As Long, Hrs As Long
If Not Intersect(Target, Columns("C:AJ")) Is Nothing Then
MyColour = Sheets("Employee List").Columns(3).Find(Cells(Target.Row, 2)).Offset(, 1)

If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = MyColour
Else
Target.Interior.ColorIndex = xlNone
End If

'Count coloured cells
For Each cel In Cells(Target.Row, 3).Resize(1, 34)
If cel.Interior.ColorIndex = MyColour Then Hrs = Hrs + 1
Next
Cells(Target.Row, "AL") = Hrs/2
End If
End Sub

[imatte]

Try the following. Insert a ColorIndex number in column D of Employee List
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyColour As Long, Hrs As Long
If Not Intersect(Target, Columns("C:AJ")) Is Nothing Then
MyColour = Sheets("Employee List").Columns(3).Find(Cells(Target.Row, 2)).Offset(, 1)

If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = MyColour
Else
Target.Interior.ColorIndex = xlNone
End If

'Count coloured cells
For Each cel In Cells(Target.Row, 3).Resize(1, 34)
If cel.Interior.ColorIndex = MyColour Then Hrs = Hrs + 1
Next
Cells(Target.Row, "AL") = Hrs/2
End If
End Sub

Okay I tried that but nothing seems to happen... did I do something wrong? Also I don't see anything in your code that formats the cell with a mouse click and or drag... when you are selecting a range of cells the cells change color so you know what you are selecting, I want to be able to do that and have it really change the color of the cells and then count them, like it looks like you were addressing in your code.

[Bob Phillips]

imatte,

I have modified Malcolm's idea a little. It is based upon assigning a colour to each of the names on the employee sheet, that is colour those cells, and then just click on the employee cells in the schedule.

[Aussiebear]

If you've downloaded Malcolm's example, try to print out his code then review your own to see where the possible error is. MD's code works for me. :clap:

[imatte]

Malcom's idea modified by XLD

As far as I can see this is solved (I'll mark it as such)... I think everything I want to do can be done with the help I have received here in this forum... Thank you

P.S. I wanted to do this for my local golf course to schedule their "cart boys" - I couldn't stand by and watch as they struggled --- Thanks again

[imatte]

I may have been a little overzealous with my rating but I'm happy; cheers!:beerchug:

[xls]

Hi, pls dont mind but i found one basic error. In a particular day (say monday only) if emp 1 is selected in first row then in remaining row for the same day, drop down list is showing name of emp 1 again. In my view it should be availble for selecting only once & if it is selected again in same day then it should show an error like "Emp already selected" etc..

[imatte]

Hi, pls dont mind but i found one basic error. In a particular day (say monday only) if emp 1 is selected in first row then in remaining row for the same day, drop down list is showing name of emp 1 again. In my view it should be availble for selecting only once & if it is selected again in same day then it should show an error like "Emp already selected" etc..
That's a good idea... they will probaly never have more than 12 employees that are scheduled in this particular sheet but it still could happen... I was planning on adding some more totalling features so I would implement that suggestion into the daily/weekly hourly max for any given part time employee

I also noticed that when you deselect or click when there was not employee selected it would total 17 hours... not sure why but I just changed the counter from:
If cel.Interior.ColorIndex = MyColour Then Hrs = Hrs + 1
To:
If cel.Interior.ColorIndex <> xlNone Then Hrs = Hrs + 1
that works... and changed the formatting to allow for half hours

The top line below the hours dissapears for some reason so I guess I will have to add some formatting to the code???

[imatte]

Hi, pls dont mind but i found one basic error. In a particular day (say monday only) if emp 1 is selected in first row then in remaining row for the same day, drop down list is showing name of emp 1 again. In my view it should be availble for selecting only once & if it is selected again in same day then it should show an error like "Emp already selected" etc..
Just noticed another thing... I added a color index to the employee list sheet and went back to edit/copy employees and selected part of the column to copy and all of my color edits disappear - this is the desired result that I was initially trying to achieve but not in the employee list sheet

[Bob Phillips]

Just noticed another thing... I added a color index to the employee list sheet and went back to edit/copy employees and selected part of the column to copy and all of my color edits disappear - this is the desired result that I was initially trying to achieve but not in the employee list sheet

Could you explain that some more, I don't see the problem?

[Bob Phillips]

That's a good idea... they will probaly never have more than 12 employees that are scheduled in this particular sheet but it still could happen... I was planning on adding some more totalling features so I would implement that suggestion into the daily/weekly hourly max for any given part time employee[/cquote]

Debra shows how to do it here http://www.contextures.com/xlDataVal03.html

[quote=imatte] I also noticed that when you deselect or click when there was not employee selected it would total 17 hours... not sure why but I just changed the counter from:
If cel.Interior.ColorIndex = MyColour Then Hrs = Hrs + 1
To:
If cel.Interior.ColorIndex <> xlNone Then Hrs = Hrs + 1
that works... and changed the formatting to allow for half hours

This is because it is counting the non coloured cells. You need to add a test if the employee name is blank, if so don't count.


The top line below the hours dissapears for some reason so I guess I will have to add some formatting to the code???

Colour is over and above gridlines, so just add bootom borders to the hours line.