Tab Color

[Mary Frances]

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?

 

[NeonRedSharpie]

Hmm, copying your code into my book works correctly. Are you sure your code is returning 0 and not "0"?

 

[shg]

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.

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

 

[VoG]

Maybe

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

 

[NeonRedSharpie]

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.

 

[shg]

<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

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