VBA won't run until cell 'activated'

[Vantiro]

I am trying to have one sheet's code turn the tab green if the value in a series of cells contains anything other than zero. The code seems to be working, but only when I activate one of the cells and hit enter. The sheet is currently set to automatic calculate and the cells are formatted to number. I am using excel 2010.

Is there a line of code I could enter to automatically do the 'activate cell'? I think there must be a better solution to get the short script to work properly.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("H16").Value = 0 And Range("L16").Value = 0 And Range("P16").Value = 0 Then
        Me.Tab.ColorIndex = -4142   ' No Color
    Else
        Me.Tab.ColorIndex = 10       ' Green
    End If
    
End Sub

 

[Vantiro]

I should also add that cells H16, L16 and P16 each contain a formula that sums up peices of data across the entire workbook. I've been searching the forums and have found similiar problems, but nothing that seems to solve the problem.

 

[JoeMo]

I should also add that cells H16, L16 and P16 each contain a formula that sums up peices of data across the entire workbook. I've been searching the forums and have found similiar problems, but nothing that seems to solve the problem.

Have you considered using worksheet activate and deactivate events? These would allow checking the specific cells each time you activate or deactivate the sheet and if they meet your criteria, color the sheet tab accordingly. You could also maintain the change code to "police" the tab color should those specific cells be changed after the sheet is activated.

 

[Vantiro]

I have not tried that. I'll run some tests using the code below and see what I come up with. Thanks!

Private Sub Worksheet_Activate()
    If Range("H18").Value = 0 And Range("L18").Value = 0 And Range("P18").Value = 0 Then
        Me.Tab.ColorIndex = -4142   ' No Color
    Else
        Me.Tab.ColorIndex = 10       ' Green
    End If
    
End Sub

 

[Vantiro]

This helps, but there is still a problem-let me explain. My workbook has about 150 worksheets that I have this code in separately for (some worksheets it needs to look at H18, other its is H16, etc.) There is also a tab for a massive data input. Basically, each of these 150 worksheets look at different parts of this data import and runs a calculation. If the worksheet is calculating something above zero in any of the three cells, I'd like it to shade the tab green so it can be easily identified from the rest of the worksheets that are calculating zero.

I appears that the activate/deactivate event will still require me to go to the tab before it will turn green. I'd like it to turn green after I import the data, assuming the worksheet is calculating a value > zero.

 

[JoeMo]

This helps, but there is still a problem-let me explain. My workbook has about 150 worksheets that I have this code in separately for (some worksheets it needs to look at H18, other its is H16, etc.) There is also a tab for a massive data input. Basically, each of these 150 worksheets look at different parts of this data import and runs a calculation. If the worksheet is calculating something above zero in any of the three cells, I'd like it to shade the tab green so it can be easily identified from the rest of the worksheets that are calculating zero.

I appears that the activate/deactivate event will still require me to go to the tab before it will turn green. I'd like it to turn green after I import the data, assuming the worksheet is calculating a value > zero.

Then maybe you should use a worksheet_calculate event rather than a change event.

 

[Vantiro]

Perfect, worksheet_calculate solves the problem. Thank you!

 

[JoeMo]

Perfect, worksheet_calculate solves the problem. Thank you!

You are welcome - thanks for the reply.