1
Thanks

A few words of thanks would be greatly appreciated.

How to Change Tab Color in Excel

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

1
Thanks

A few words of thanks would be greatly appreciated.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « How to Change Tab Color in Excel », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).