Excel - Date formula and Row Highlighting

[ABrown]

Hi - I wonder if someone can help. I am putting together a spreadsheet which is almost complete except for two small issues - one is I need the active row highlighted and I have used the following code:

[vba]
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static rr
Static cc
If cc <> "" Then
With Columns(cc).Interior
.ColorIndex = xlNone
End With
With Rows(rr).Interior
.ColorIndex = xlNone
End With
End If
r = Selection.Row
c = Selection.Column
rr = r
cc = c
With Columns(c).Interior
.ColorIndex = 20 .Pattern = xlSolid
End With
With Rows(r).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With
End Sub[/vba]

But in the spreadsheet the pasting stops working if I add this to the Workbook.

Secondly is a formula - I have two dates/times - formatted for time from which I take the earlier date away from the later date giving me the elapsed time (ie the time spent working on a project). I then format this for hh:mm, but I also need to display it is periods of 6 minutes, rounded up to the nearest 6 minutes.

Can anyone help with either problem.

Many thanks.

Annette

[Simon Lloyd]

I haven't looked at your workbook, i have just sorted the code you posted to something we can read, where are you getting your variables populated from?, you also set r to be selection.row....etc and then make rr the same value but then you don't use rr?

[ABrown]

Many thanks - have found better way:
[VBA]
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.Interior.ColorIndex = xlColorIndexNone
ActiveCell.EntireRow.Interior.ColorIndex = 36
End Sub
[/VBA]
But still get error when I try and paste something into an active cell that contains highlight - any clues on this or the time issue?

Very many thanks.

Annette

[p45cal]

The code to colour the cells cancels the copy/paste, just as it would do if you chose manually to colour a cell in mid copy/paste operation.
Adding this line:[vba]If Application.CutCopyMode = 1 Then Exit Sub[/vba]as the first line of the sub will cause the sub to be exited without doing anything and will allow pasting to happen.. but highlighting of the active row/column won't happen either, until you change the selection again. If you can live with that, then that's a possible solution.

Simon,
The cc and rr are static variables, created and set within the sub itself. They serve to retain (between separate calls to the sub) last column and row selected so that only they get their colour cleared rather than the whole sheet as in Annette's so-called 'better way'.

[p45cal]

regarding dates, the two cells containg the date/times are in cells A1 and A2, then in any other cell:
=CEILING((A2-A1)*240,1)/240

(1/240 of a day is 6 minutes)

ps. if some times go over 24 hours, it would be safer to format the cell with the result in as
[hh]:mm
rather than
hh:mm

[Simon Lloyd]

Annette, if the workbook is just for you you can add this code and assign a button to it, it will toggle workbook events on/off allowing you to paste.
[VBA]Sub allow_paste()
If Application.EnableEvents = True Then
Application.EnableEvents = False
Else
Application.EnableEvents = True
End If
End Sub[/VBA]first click allows you to paste, the next click turns the events back on so highlighting works!

[Benzadeus]

Simon, what about using
[VBA]Sub allow_paste()
Application.EnableEvents = Not Application.EnableEvents
End Sub[/VBA]

to do same thing of your code?

[Simon Lloyd]

Benzadeus, yes thats correct, although that one liner would take some explanation to the OP where most can readily understand and follow and if statement