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

July 2017



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


Published by aakai1056. Latest update on November 6, 2012 at 08:19 AM by Jeff.
This document, titled "Excel/VBA - A macro to change the color of a row," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).