Changing the sheet tab color based on the value of a cell

Hi Guys,

I am using excel 2010 and I want the color of my tabs to be changed to green if the value of cell M3 is 2.

I have uploaded the file in here. Since I tried inserting the macros myself and it didn't work, it is appreciated if you place it in the my excel file and upload it again. Alternatively, you may email it to [email protected] as well.

Thank you very much

Paymet Request List 1.xlsPaymet Request List 1.xls

Originally Posted by Solus Rankin

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Not Intersect(Target, Sh.Range("M3")) Is Nothing Then
    If Sh.Range("M3").Value = 2 Then
        Sh.Tab.Color = 5287936
    Else
        Sh.Tab.ColorIndex = -4142
    End If
End If

End Sub

Place this in the workbook module. I can't place it for you because I can't download from the site you uploaded to.

To attach a workbook here, click the 'Go Advanced' button below and use the paperclip in the toolbar to navigate to your document.

It doesn't work...I attached it as instructed...can you please place it in my excel file?

Thx

Originally Posted by Mad-Mizer

Perhaps the following is hat you need:

Sub SetSheetColour()
    Dim sht As Worksheet
    
    For Each sht In Sheets
        If sht.Range("M3").Value = 2 Then
            With sht.Tab
                .Color = 5296274
            End With
        End If
    Next sht
End Sub

Thank you. It somehow works. But the problem is that all tabs just turn to green and their color doesn't change based on the value of cell M3!

Originally Posted by Mad-Mizer

Perhaps you should upload your workbook to this site, I cannot access the other one due to company restrictions.

Sure. You can download it from here. I am also attaching it to this post.

It is appreciated if you place it in it and send it back.

Thank you

I don't really know what's wrong, I have the same issue with the file that Solus posted. Mostly they are just green and don't change. Sometimes, only 1 tab turn to green and other tabs stay as they are!! I tried on both Excel 2007 on Windows XP and Excel 2010 on Windows 7 and the result was the same!

I am uploading 2 screen shots.

Excel 2.jpgExcel.jpg