Excel/VBA - A macro to change the color of a row

November 2016



Issue


I've a spreadsheet when I need to change the row color dependent on the data entered into a particular cell.
The current VBA code, below, only changes the individual cell and not the row.

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPlage = Range("B13:I50")
For Each Cell In MyPlage

If Cell.Value = "Withdrawn" Then
Cell.Interior.ColorIndex = 7
End If

If Cell.Value = "Postponed" Then
Cell.Interior.ColorIndex = 8
End If

If Cell.Value = "Terms Agreed" Then
Cell.Interior.ColorIndex = 4

End If
If Cell.Value = "Papers Rec" Then
Cell.Interior.ColorIndex = 3
End If

If Cell.Value <> "Withdrawn" And Cell.Value <> "Postponed" And Cell.Value <> "Terms Agreed" And Cell.Value <> "Papers Rec" Then
Cell.Interior.ColorIndex = xlNone
End If

Next
End Sub

However I now need to amend this so that the row changes to the same color as the cell. Any help gratefully appreciated.

Solution


Try this code:

Private Sub Worksheet_Change(ByVal Target As Range)   

    Set MyPlage = Range("B13:I50")  
      
    For Each Cell In MyPlage  
      
        Select Case Cell.Value  
          
         Case Is = "Withdrawn"  
            Cell.EntireRow.Interior.ColorIndex = 7  
          
        Case Is = "Postponed"  
            Cell.EntireRow.Interior.ColorIndex = 8  
                  
        Case Is = "Terms Agreed"  
            Cell.EntireRow.Interior.ColorIndex = 4  
              
        Case Is = "Papers Rec"  
            Cell.EntireRow.Interior.ColorIndex = 3  
              
        Case Else  
            Cell.EntireRow.Interior.ColorIndex = xlNone  
          
        End Select  
      
    Next  
End Sub  

Note that


Solved by rizvisa1

Related :

This document entitled « Excel/VBA - A macro to change the color of a row » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.