Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Range("$T2:$T5000")
Select Case oCell.Value
Case Is = "Closed"
oCell.EntireRow.Interior.Color = RGB(196, 215, 155)
Case Is = "Recalled"
oCell.EntireRow.Interior.Color = RGB(191, 191, 191)
Case Is = "Open"
oCell.EntireRow.Interior.ColorIndex = xlNone
End Select
Next oCell
End Sub
But now I have a new challenge. When I receive an document for review and its been reviewed, I record the Reviewed Date in Column "O" for that document.Do more with
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Range("$T2:$T5000")
Select Case oCell.Value
Case Is = "Closed"
oCell.EntireRow.Interior.Color = RGB(196, 215, 155)
Case Is = "Recalled"
oCell.EntireRow.Interior.Color = RGB(191, 191, 191)
Case Is = "Open"
If Cells(oCell.Row, "O").Value = vbNullString Then
oCell.EntireRow.Interior.Color = vbYellow
Else
oCell.EntireRow.Interior.ColorIndex = xlNone
End If
End Select
Next oCell
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("T2:T5000")) Is Nothing Then
For Each oCell In Target
Select Case oCell.Value
Case Is = "Closed"
oCell.EntireRow.Interior.Color = RGB(196, 215, 155)
Case Is = "Recalled"
oCell.EntireRow.Interior.Color = RGB(191, 191, 191)
Case Is = "Open"
If Cells(oCell.Row, "O").Value = vbNullString Then
oCell.EntireRow.Interior.Color = vbYellow
Else
oCell.EntireRow.Interior.ColorIndex = xlNone
End If
End Select
Next oCell
End If
End Sub
Premium Content
You need an Expert Office subscription to comment.Start Free Trial