CODEWITHSUNDEEP
excelvba
sbagnato
sbagnato

Reputation: 496

Highlight row, but not entire row

I have a workbook that contains daily login information for my analysts. I have certain conditional highlighting set up based on various parameters. What I am trying to add is code that will search a column (H), and if the value is 0 (meaning the analyst was not logged in that day), then the conditional highlighting for that row is removed, and that row is changed to red. However, since the row only extends from col A to col R, I do not want S and beyond to be highlighted. Below is the code I have so far, which works until the very end, as it highlights the whole row. I know the issue is EntireRow, but I am not sure how to get it to be A#:R#, with # being the row that corresponds to 0 in col H.

'Account for any OOO
    Dim srng As Range
    For Each srng In Range("H2:H8")
        If srng.Value = 0 Then
            srng.EntireRow.FormatConditions.Delete
            srng.EntireRow.Interior.Color = 5263615
        End If
    Next srng

EDIT::::::::

What I would like for it to do in the end is to come to a row where col H is 0, then prompt me to account for if the analyst was scheduled off or not. If yes, highlight the row in gray; if no highlight red. The below code is not quite correct, but I tried..

'Account for any OOO
    Dim schdOff As Range
    For Each schdOff In Range("H2:H8")
        If schdOff.Value = 0 Then
            Dim int1 As Integer
            Dim sPrompt As String
            sPrompt = "Was the analyst scheduled to be off yesterday?"
            int1 = MsgBox(sPrompt, vbYesNo)
                If int1 = vbYes Then
                    Cells(schrng.Row, 1).Resize(, 18).FormatConditions.Delete
                    Cells(schrng.Row, 1).Resize(, 18).Interior.Color = 11711154
                Else
                    Cells(srng.Row, 1).Resize(, 18).FormatConditions.Delete
                    Cells(srng.Row, 1).Resize(, 18).Interior.Color = 5263615
                End If
        End If
    Next schdOff

Upvotes: 1

Views: 1274

Answers (1)

SJR
SJR

Reputation: 23081

Try this (though why not use conditional formatting?)

'Account for any OOO
    Dim srng As Range
    For Each srng In Range("H2:H8")
        If srng.Value = 0 Then
            cells(srng.Row,1).resize(,18).FormatConditions.Delete
            cells(srng.Row,1).resize(,18).Interior.Color = 5263615
        End If
    Next srng

Upvotes: 2

Related Questions