tab color based upon cell value in the sheet

[twf matrix]

I need the tab color to be determined by a value in the group of cells d9 thru d100. If there is a 1 then the tab is to be red, if the lowest number is 2 then tab to be yellow, if lowest number is 3 then tab to be green. Each worksheet tab color could be different depending on the lowest number in that column. Do macro's need to be enabled for this to work?
I appreciate the help to solve this dilemma.

 

[dave3009]

Hi twf matrix, welcome to the board.

I'm not sure of a native excel conditional formatting for tab colour, but if you enable macros and run this it should do as you request.

Sub changeTabColour()


Dim rng As Range
Dim sht As Worksheet


For Each sht In Worksheets


    Set rng = sht.Range("D9:D100")
    Select Case WorksheetFunction.Min(rng)
        Case Is = 1:
            sht.Tab.Color = vbRed
        Case Is = 2:
            sht.Tab.Color = vbYellow
        Case Is = 3:
            sht.Tab.Color = vbGreen
    End Select


Next sht


End Sub

 

[twf matrix]

Can macros be "disable all macros with notification", and trust access to the vba project checked?
I am using excel 2010

 

[twf matrix]

Also if not all cells d9-d100 have values does it matter?

 

[dave3009]

Hi

You can set the macro security as you've said, but you'd need to enable the content as per the notification.

If there are empty cells then that is fine, it will evaluate the lowest actual number.

Regards

Dave