Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Excel 2003 - Change Row Colour with VBA [Solved/Closed]

Report
-
 StevieB -
Hi, hope somebody can help

I've got a spreadsheet when I need to change the row colour 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 colour as the cell.

Any help gratefully appreciated.


Steve

See more

1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 760 
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
Report

Worked like a charm.

Thanks for a swift response!
Ask a question