Excel is one of the most useful tools but it may be quickly confusing for beginners, especially for those who don’t have much computer knowledge background. If you want to change the color of a row based on a given value, then you are in the right place: in this article we will show an example of the typical problem of changing the color of a row in Excel with a MACRO.
Excel: Change Cell Color Based on Value
You have a spreadsheet and you need to change the row color dependent on the data entered into a particular cell but the current VBA code (shown below) only changes the individual cell and not the whole row.
Incorrect code:
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
Solution
Here is a solution on how to change tab color in Excel depending on a value:
Right 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
Image: © Everypixel
