Highlight range of cells based on conditional value

Question

I'm trying to find a VBA code that would highlight appropriate row within the range "A7:AD100" if a cell in the column "AB" has value "Elective."

Sub highlight()    
    Dim cell As Range
    Range(Range("AB7"), Range("AB7").End(xlDown)).Select
    For Each cell In Selection
        If cell = "Elective" Then Cells.Range($A7, $AD7).Interior.ColorIndex = 10
    Next cell
End Sub

Only rows 1, 11, 21, 23 are highlighted since they have Admit Type = "Elective". The rows highlighted only within the range "A:AD" (I don't want the whole row to be highlighted).

Denis Grela Denis Grela · Accepted Answer · 2019-04-07T21:10:45

I found this code that works for me

Sub HighlightCells()

Dim rngMyCell  As Range
Dim lngLastRow As Long

Application.ScreenUpdating = False

lngLastRow = Cells(Rows.Count, "AB").End(xlUp).Row

For Each rngMyCell In Range("AB7:AB" & lngLastRow)
    If StrConv(rngMyCell, vbProperCase) = "Elective" Then
        Range("A" & rngMyCell.Row & ":AD" & rngMyCell.Row).Interior.Color = RGB(240, 240, 240)
    End If
Next rngMyCell

Application.ScreenUpdating = True

End Sub

Highlight range of cells based on conditional value

1 Answers