Excel Tab Color

raajnabriz

Board Regular
Joined
Jun 11, 2014
Messages
112
hi, would like to have a formula or vb code for the following

if a cell value is 0 [zero] the color of the tab should change to yellow
if a cell value is >0 it should change to red

please your help is requested
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
hi, would like to have a formula or vb code for the following

if a cell value is 0 [zero] the color of the tab should change to yellow
if a cell value is >0 it should change to red

please your help is requested
1. Which cell?

2. Does that cell contain a formula or is it being altered manually, or by code, some other way?

3. If it contains a formula, what is the formula?
 
Upvote 0
hi, would like to have a formula or vb code for the following

if a cell value is 0 [zero] the color of the tab should change to yellow
if a cell value is >0 it should change to red

please your help is requested
Hi raajnabriz

I had just written some code to do what you had requested when I saw Peter's questions which prompted me to make a second version of the code so there is a version for each possibility.

Both of the following codes are applied directly to the backend of the sheet in question rather than to a standard module. To do this simply right-click on the tab name and select View Code. In the new window that opens copy paste in one of the following codes (depending on what the answers to Peter's questions are), then save the document as a macro enabled workbook (.xlsm format). Both of these macros assume the value to check is in cell A1 and that the tab to be coloured is called Sheet1. In both cases you can amend these details to suit your data.

For manually entered cell values:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 0 Then
    Sheets("Sheet1").Tab.ColorIndex = 6
ElseIf Range("A1").Value > 0 Then
    Sheets("Sheet1").Tab.ColorIndex = 3
End If
End Sub

For cell values reached as a result of a formula:

Code:
Private Sub Worksheet_Calculate()
If Range("A1").Value = 0 Then
    Sheets("Sheet1").Tab.ColorIndex = 6
ElseIf Range("A1").Value > 0 Then
    Sheets("Sheet1").Tab.ColorIndex = 3
End If
End Sub
 
Upvote 0
For manually entered cell values:
I would consider adding a section to remove the tab colour if the cell does not meet either of those two conditions. Otherwise the tab colour could mislead the user about what was in the cell.


For cell values reached as a result of a formula:
The reason I asked for the formula was that, if simple to see the precedents, it would be better (IMHO) to still use WorkSheet_Change, but use those precedent cells as the 'Target', rather than recalculating the tab colour every time the sheet recalculates for any reason.

Edit: Also, presumably the cell in question is on the sheet for which the tab is to be coloured, in which case it would be better to use Me. instead of Sheets("Sheet1"). so you don't have to edit the code if the sheet name changes.


@raajnabriz Could you still answer my Q2 and Q3?
 
Last edited:
Upvote 0
I would consider adding a section to remove the tab colour if the cell does not meet either of those two conditions. Otherwise the tab colour could mislead the user about what was in the cell.


The reason I asked for the formula was that, if simple to see the precedents, it would be better (IMHO) to still use WorkSheet_Change, but use those precedent cells as the 'Target', rather than recalculating the tab colour every time the sheet recalculates for any reason.

Edit: Also, presumably the cell in question is on the sheet for which the tab is to be coloured, in which case it would be better to use Me. instead of Sheets("Sheet1"). so you don't have to edit the code if the sheet name changes.
One day I'll learn to cover all my bases, but I am slowly getting there :)

hi Fishboy, your formula works wonderful, thanks for your help as well as Mr. Peter.
Happy to have helped, but as usual Peter makes some very valid points which I had not considered in my haste to reach a solution. If you are still able to answer Peter's questions we may need to / benefit from tweaking my code accordingly.
 
Upvote 0
Yes Mr. Peter, there is a formula "countif" in the cell which is =COUNTIF(D2:D69,"PENDING")
its an excel sheet wherein i have to receive patient files, so at the beginning "d2:d69" is pending and shows the number of files to be received and when i receive all the files the value in that particular cell will be 0

now the cell differs in every sheet. thanks
 
Upvote 0
Yes Mr. Peter, there is a formula "countif" in the cell which is =COUNTIF(D2:D69,"PENDING")
OK, assuming that D2:D69 are not also formulas, then we should still be able to use WorkSheet_Change rather than _Calculate.

The fact that the cell contains that particular formula removes the first issue I raised previously as the cell will only ever hold zero or a positive integer.

So if you are manually changing cells in D2:D69 as you receive the files then you could try the following, changing the A1 to whatever the cell actually is that contains that COUNTIF formula.

Rich (BB code):
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D2:D69")) Is Nothing Then
    If Range("A1").Value = 0 Then
      Me.Tab.ColorIndex = 6
    Else
      Me.Tab.ColorIndex = 3
    End If
  End If
End Sub
 
Upvote 0
Yes Mr. Peter, the given code worked perfectly,
many thanks to you as well to Mr. Fishboy.

raajnabriz.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top