Excel Calculation in Cell for certain specified ce

Question

I work on Excel-2010 and Excel-2013. I want to do something like below.

Whatever the number I enter in cell, that number must be devided by 60 and the result should be printed on the same cell upon pressing TAB. If I come back to this cell, the entered number should re-appear, not the result of the calculation.

I have got nice answer from Kresimir L here. But I am unable to apply his logic for my own requirement. My Excel sheet looks like below:

User has to enter his effort against the given date. (Please ignore the color coding for now). For example, if user has entered 45, which means 45 minutes against certain date, then this cell should be converted to 0.75 which is in the hours format. Please note that, this calculation is NOT required for all rows but 12th, 14th, 16th, 18th, 20th row etc.

One more thing, this worksheet is for 2017. I need to apply this same technique for next consecutive years also. Hence, how can I make this formula work for multiple worksheets?

I don't know much about programming in Excel. I can't open most of the sites due to company security policies.

Can anyone please help me on this!

Answer 1

I could able to achieve this solution with the help of multiple brilliants. Collected different answers from different people, and formed the below answer. Posting for others' reference who are in real need.

Option Explicit

Dim DivRg1, DivRg2, DivRg As Range

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    Set DivRg1 = Sheet14.Range(Cells(10, 1), Cells(10, 20))
    Set DivRg2 = Sheet14.Range(Cells(12, 1), Cells(12, 20))
    Set DivRg = Application.Union(DivRg1, DivRg2)
    'For Each element In DivRg
        'MsgBox element
    'Next
    If (Target > 2) Then
        Application.EnableEvents = False
        Target = Target / 60
        Target = WorksheetFunction.Floor(Target, 0.01) 'Rounding the result to two decimals
        'Target.Offset(0, 5).Value = 1
        Application.EnableEvents = True
    End If
    Set DivRg = Nothing
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    Set DivRg1 = Sheet14.Range(Cells(10, 1), Cells(10, 20))
    Set DivRg2 = Sheet14.Range(Cells(12, 1), Cells(12, 20))
    Set DivRg = Application.Union(DivRg1, DivRg2)

    Dim iSect As Range
    Set iSect = Application.Intersect(Target, DivRg) 'Here, iSect gives me the value in the cell
    If Not (iSect Is Nothing) And (iSect < 2#) Then
        Application.EnableEvents = False
        iSect = iSect * 60
        If (iSect = 0) Then iSect = Empty
        Application.EnableEvents = True
    End If
    Set DivRg = Nothing
End Sub