Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Excel Tab Color

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    76

    Default Excel Tab Color

    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

  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    34,169

    Default Re: Excel Tab Color

    Quote Originally Posted by raajnabriz View Post
    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?
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  3. #3
    Board Regular Fishboy's Avatar
    Join Date
    Feb 2015
    Location
    UK
    Posts
    4,261

    Default Re: Excel Tab Color

    Quote Originally Posted by raajnabriz View Post
    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
    REMEMBER: Always test out new code / formulas in a COPY of your worksheet as some actions CANNOT be undone by clicking the "Undo" button

    If you need to share example data, a number of useful items including the Mr.Excel HTML Maker
    can be found here.

    If someone took the time and helped you to solve your issue remember to click the "Like" button on their post to make it easier for others for find the right solution, plus a little gratitude goes a long way!

  4. #4
    Board Regular
    Join Date
    Jun 2014
    Posts
    76

    Default Re: Excel Tab Color

    hi Fishboy, your formula works wonderful, thanks for your help as well as Mr. Peter.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    34,169

    Default Re: Excel Tab Color

    Quote Originally Posted by Fishboy View Post
    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.


    Quote Originally Posted by Fishboy View Post
    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 by Peter_SSs; Dec 8th, 2015 at 08:27 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  6. #6
    Board Regular Fishboy's Avatar
    Join Date
    Feb 2015
    Location
    UK
    Posts
    4,261

    Default Re: Excel Tab Color

    Quote Originally Posted by Peter_SSs View Post
    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

    Quote Originally Posted by raajnabriz View Post
    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.
    REMEMBER: Always test out new code / formulas in a COPY of your worksheet as some actions CANNOT be undone by clicking the "Undo" button

    If you need to share example data, a number of useful items including the Mr.Excel HTML Maker
    can be found here.

    If someone took the time and helped you to solve your issue remember to click the "Like" button on their post to make it easier for others for find the right solution, plus a little gratitude goes a long way!

  7. #7
    Board Regular
    Join Date
    Jun 2014
    Posts
    76

    Default Re: Excel Tab Color

    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

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    34,169

    Default Re: Excel Tab Color

    Quote Originally Posted by raajnabriz View Post
    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.

    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
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007, 2003 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  9. #9
    Board Regular
    Join Date
    Jun 2014
    Posts
    76

    Default Re: Excel Tab Color

    Yes Mr. Peter, the given code worked perfectly,
    many thanks to you as well to Mr. Fishboy.

    raajnabriz.

  10. #10
    Board Regular Fishboy's Avatar
    Join Date
    Feb 2015
    Location
    UK
    Posts
    4,261

    Default Re: Excel Tab Color

    Quote Originally Posted by raajnabriz View Post
    many thanks to you as well to Mr. Fishboy.
    No worries
    REMEMBER: Always test out new code / formulas in a COPY of your worksheet as some actions CANNOT be undone by clicking the "Undo" button

    If you need to share example data, a number of useful items including the Mr.Excel HTML Maker
    can be found here.

    If someone took the time and helped you to solve your issue remember to click the "Like" button on their post to make it easier for others for find the right solution, plus a little gratitude goes a long way!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com