tab color based upon cell value in the sheet

twf matrix

New Member
Joined
Mar 17, 2016
Messages
3
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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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.

Code:
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

New Member
Joined
Mar 17, 2016
Messages
3
Can macros be "disable all macros with notification", and trust access to the vba project checked?
I am using excel 2010
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top