• 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

      Identifying days based on date values in VBA

      I have dates along with time under Col K and certain values (numbers) corresponding to these days under Col M.

      I have a code that changes the color of these values if they are greater than 1 and if they have a text “waiting” in col P.

      What I dont know to do is, add the below condition into this code:

      1. I want to identify if these days belongs to a sunday.
      2. If Yes, then I want to check if the Sunday hours (lets say the date/time format is “15/1/2016 17:00” so the remaining time left for sunday to get over is 0.3 day) subtracted from the number in Col M and if still the number is >1, then it should be highlighted in “Red”.
      3. The subtraction should not affect or appear in the current sheet.

       

      Sub LatencyMarker()
      Dim r As Long
      Dim m As Long
      On Error GoTo ExitHere:
      m = Range("M:P").Find(What:="*", SearchOrder:=xlByRows,  SearchDirection:=xlPrevious).Row
      Application.ScreenUpdating = False
      For r = 1 To m
      If Range("P" & r) = "waiting" Then
      If Range("M" & r) >= 1 Then
      Range("M" & r).Cells.Font.ColorIndex = 3
      Else
      Range("M" & r).Cells.Font.ColorIndex = 0
      End If
      End If
      Next r
      ExitHere:
      Application.ScreenUpdating = True
      End Sub
      
      Asked by silverblade on January 20, 2017 in VBA: Programming.
      2 Answers
      Keymaster

      Hi,

      Please check this code, this should help to achieve your requirement:

      Sub LatencyMarker()
      Dim r As Long
      Dim m As Long
      On Error GoTo ExitHere:
      m = Range("M:P").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
      Application.ScreenUpdating = False
      For r = 1 To m
      remainingDay = 0
      If Weekday(Range("K" & r)) = 1 Then
      remainingDay = Round((24 - Format(TimeValue(Range("K" & r)), "h")) / 24, 1)
      End If
      If Range("P" & r) = "waiting" 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
      Next r
      ExitHere:
      Application.ScreenUpdating = True
      End Sub
      

      Thanks!

      Answered by PNRao on January 20, 2017..
      Top Contributor

      Hi, thanks for the code. But I’m not getting any result even if the condition is met. I have attached a sample sheet here – http://www.megafileupload.com/8hwq/sample.xlsx. Please review it. Thanks in advance.

      Answered by silverblade on January 20, 2017..