• Ask a Question
150
Insert Image Size must be less than < 5MB.
    Ask a Question
    Cancel
    150
    More answer You can create 5 answer(s).
      Ask a Poll
      Cancel
      Top Contributor

      Subtract remaining hour from a day using VBA

      I have the below code that will identify a Sunday and highlight values in Col M if they are greater than 1 and has the text “waiting” in col P.

      What I want to do is:

      1. I have the date and time format in MM/D/YYYY TIME (example) – 1/22/2017 23:30
      2. I want to subtract the Sunday time of the date with a cut of time of 23:59 and that remaining number should be subtracted with the value in col M and if still the value is greater than col M then it should be highlighted in Red.

      Example:

      If the date/time is 1/22/2017 21:00 in col K, then the remaining hour here is 0.3 hours..this should be subtracted from the value in col M, lets say col M has 1.3, so 1.3-0.3 = 1. So it should be highlighted.

       

      Sub SundayDatefilter()
      Dim r, lastrow, remainingDay As Long
      lastrow = Range("M:P").Cells(Rows.count, "A").End(xlUp).Row
      Application.ScreenUpdating = False
      For r = 2 To lastrow
      remainingDay = 0
      If Weekday(Range("K" & r).Value, vbSunday) = 1 Then
      remainingDay = Round((24 - Format(TimeValue(Range("K" & r)), "h")) / 24, 1)
      If InStr(1, Range("P" & r).Text, "waiting", vbTextCompare) > 0     Then
      If Range("M" & r) - remainingDay >= 1 Then
      Range("M" & r).Cells.Font.ColorIndex = 3
      Else
      Range("M" & r).Cells.Font.ColorIndex = 0
      End If
      End If
      End If
      Next r
      Application.ScreenUpdating = True
      End Sub
      Asked by silverblade on January 25, 2017 in VBA.
      0 Answers