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

Question

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

Report

StevieB - Jul 22, 2010 at 10:49 AM
StevieB - Jul 25, 2010 at 01:17 PM

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

Excel 2003 - Change Row Colour with VBA
If statement using date to change row colour ✓ - Forum - Excel
Compare two lists and change row colour ✓ - Forum - Office Software
Excel vba change row color - How-To - Excel
Excel - Compare two lists and change row color - How-To - Excel
Vba excel insert multiple rows - How-To - Excel

1 reply

Ask a question

rizvisa1 · Answer 1 · 2010-07-22T14:03:12-0400

Answer 1 / 1

rizvisa1

Posts: 4476
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: August 2, 2020

764
Jul 22, 2010 at 02:03 PM

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

StevieB
Jul 25, 2010 at 01:17 PM

Worked like a charm.

Thanks for a swift response!

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

1 reply

Your reply

Subscribe To Our Newsletter!