Tab Color

Mary Frances

New Member
Joined
Jan 19, 2006
Messages
8
I have created a workbook for our quotation process. If all the info is filled in for row 1, J1 has a formula that returns 0, if info is missing, J1 returns 1. All the rows are added into J70. If J70 is >0, something is missing. If J70<1 everything is filled in. The Sheet Tab is red. I found some code on a website that will turn the tab while if J70<1. To test, I deleted the formula in J70, the tab turns white. However, if I go through and fill out the form, thus making J70=0, the tab stays red. Here is the code I used:

Private Sub Worksheet_Change(ByVal Target As Range)

With Me
Select Case .Range("J70").Value

Case Is < 1
.Tab.ColorIndex = xlNone

Case Is > 0
.Tab.Color = vbRed


End Select
End With
End Sub

Could the reason be that J70 is volitle?
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,836
Office Version
  1. 2010
Platform
  1. Windows
It never gets to the second test. Case statements are processed sequentially. If J70 is 0, it's < 1, so only the first Case ever executes.

Code:
    With Me
        Select Case .Range("J70").Value
            Case 0
                .Tab.ColorIndex = xlColorIndexNone
            Case Else
                .Tab.Color = vbRed
        End Select
    End With
 
Last edited:
Upvote 0

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe
Code:
Private Sub Worksheet_Calculate()
With Me
Select Case .Range("J70").Value

Case Is < 1
.Tab.ColorIndex = xlNone

Case Is > 0
.Tab.Color = vbRed


End Select
End With
End Sub
 
Upvote 0

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
It never gets to the second test. Case statements are processed sequentially. If J70 is 0, it's < 1, so only the first Case ever executes.

Change < and > to =

<1 includes 0 but >0 does not include 0. Both will get executed. If it was <=1 and >=0 then you would be correct.
 
Upvote 0

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,836
Office Version
  1. 2010
Platform
  1. Windows
<1 includes 0 but >0 does not include 0. Both will get executed. If it was <=1 and >=0 then you would be correct.
Sorry, you're exactly correct.

I recommend my code, though, or just

Code:
    With Me
        If .Range("J70").Value = 0 Then .Tab.ColorIndex = xlColorIndexNone Else .Tab.Color = vbRed
    End With
 
Upvote 0

Forum statistics

Threads
1,190,677
Messages
5,982,211
Members
439,768
Latest member
loukrs

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