0

I'm trying to make a row change colour to yellow based on if any text is within the D column. I have already set up 5 different colours for another cell, however need this to over-ride those ones. I can make this work if I say ‘YES’ as it’s a pre-determined value. However, I need to input various text and numbers so that doesn’t surfice.

Below is what I have currently gathered but need to change the ‘YES’ so its any text within that cell.

Private Sub Worksheet_Change(ByVal Target As Range)

Set MyPlage = Range("T8:T1000") 
For Each Cell In MyPlage
        Select Case Cell.Value  
     Case Is = "Cancelled"
        Cell.EntireRow.Interior.ColorIndex = 8
     Case Is = "Rejected"
        Cell.EntireRow.Interior.ColorIndex = 3           
    Case Is = "Completed"
        Cell.EntireRow.Interior.ColorIndex = 4
    Case Is = "Pending"
        Cell.EntireRow.Interior.ColorIndex = 15          
    Case Is = "Accepted"
        Cell.EntireRow.Interior.ColorIndex = 39
    Case Else
        Cell.EntireRow.Interior.ColorIndex = xlNone

    End Select

Next

 Set MyPlage = Range("D8:D1000")
 For Each Cell In MyPlage
        Select Case Cell.Value
     Case Is = "YES"
        Cell.EntireRow.Interior.ColorIndex = 6

    End Select
Next

End Sub

Thanks James

0

Basically you don't want to do a select case just check if the cell is empty

Sub run()

 Set myplage = Range("D8:D1000")
 For Each cell In myplage
    If Not IsEmpty(cell.Value) Then
        cell.EntireRow.Interior.ColorIndex = 6
    End If
Next

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.