VBA won't run until cell 'activated'

Vantiro

New Member
Joined
Jul 25, 2014
Messages
15
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.

Code:
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Vantiro

New Member
Joined
Jul 25, 2014
Messages
15
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 25, 2014
Messages
15
I have not tried that. I'll run some tests using the code below and see what I come up with. Thanks!

Code:
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

New Member
Joined
Jul 25, 2014
Messages
15

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,774
Members
413,934
Latest member
austinb

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