0

I have code that searches a column H from time values. If the value is 0, then a prompt asks me if the analyst was scheduled off, or not. If yes, highlight the row in grey, if not, highlight in red. If the value is above 0, but below 8 hours and 58 minutes, the prompt asks if the analyst was scheduled to be short time or not. If yes, it is supposed to highlight the specific cell in col H grey, and if no, it is supposed to highlight in red. This entire process is working except for that last part. If I hit yes for short time, it marks the cell red, and if I hit no for short time, it still marks it red. Any ideas?

'Account for any OOO
    Dim schdOff As Range
    For Each schdOff In Range("H2:H8")
        If schdOff.Value > 0 And schdOff.Value < 0.373611111111111 Then
            Dim int1 As Integer
            Dim stPrompt As String
            Cells(schdOff.Row, 1).Resize(, 1).Interior.Color = 65535
            stPrompt = "Was the highlighted analyst scheduled to be short time?"
            in1 = MsgBox(stPrompt, vbYesNo)
                If int1 = vbYes Then
                    schdOff.FormatConditions.Delete
                    schdOff.Interior.Color = 11711154
                Else
                    schdOff.FormatConditions.Delete
                    schdOff.Interior.Color = 5263615
                End If
            Cells(schdOff.Row, 1).Resize(, 1).Interior.Color = 16777215
        ElseIf schdOff.Value = 0 Then
            Dim int2 As Integer
            Dim sPrompt As String
            Cells(schdOff.Row, 1).Resize(, 18).Interior.Color = 65535
            sPrompt = "Was the highlighted analyst scheduled to be off?"
            int2 = MsgBox(sPrompt, vbYesNo)
                If int2 = vbYes Then
                    Cells(schdOff.Row, 1).Resize(, 18).FormatConditions.Delete
                    Cells(schdOff.Row, 1).Resize(, 18).Interior.Color = 11711154
                Else
                    Cells(schdOff.Row, 1).Resize(, 18).FormatConditions.Delete
                    Cells(schdOff.Row, 1).Resize(, 18).Interior.Color = 5263615
                End If
        End If
    Next schdOff
Community
  • 1
  • 1
sbagnato
  • 603
  • 3
  • 11
  • 35
  • 2
    Add `Option Explict` to the top of your module and declare all your variables. When you do, you'll find your typo - you set the return value of the `MsgBox` to `in1`, but base your `If` statement on `int1`. – Comintern Jan 10 '17 at 20:56
  • That was it! Thank you. Dang typos – sbagnato Jan 10 '17 at 20:59
  • 3
    "Dang typos" is just one reason to use `Option Explicit` [see this answer for some others](http://stackoverflow.com/a/30794674/4088852). – Comintern Jan 10 '17 at 21:01

0 Answers0