Change Tab Color Based On One time Cell Value

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
Hello,

I have Windows 7 and Excel 2010.

Currently I have a macro which checks two reports against each other to ensure all data is in both reports. If they match, J1 has a value of 0 (or very close, like '8.28003976494074E-09') .

There is a tab for each entity, so rather than having to go through every tab to check and see if the value in J1 is '-' (comma style 0) or '0.00' (for the values that round to 0), I'd like to have my macro just turn these tabs green.

I've looked over many articles, but none of them really do what I would need, as many of the codes I've found are their own sub, are placed in the code for the individual sheet, and are able to change the color every time the cell value is changed.

I would just like some code to throw on the end of my existing macro to check one time if the values in J1 for each tab are 0 or round to 0 and if so, then turn the tabs green, and that's it. Is this possible?

Thank you in advance for your time!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Try:
Code:
Sub FormatTabs()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    With ws
        If Abs(.Range("J1").value < 0.0001) And Len(.Range("J1").value) > 0 Then
            .Tab.ColorIndex = 4
        End If
    End With
Next ws

Application.ScreenUpdating = True

End Sub
 

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
Hello Jack,

Thank you for the quick reply.

Your code looks like it should work, but nothing happens when I run it =/. Any ideas? Do you need any additional information? I can provide tab names if that would help.
 

healey21

Well-known Member
Joined
Dec 22, 2009
Messages
900
What if you add ws.activate so it goes to the first sheet then moves on

Sub FormatTabs()

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
ws.Activate
With ws
If Abs(.Range("J1").Value < 0.0001) And Len(.Range("J1").Value) > 0 Then
.Tab.ColorIndex = 4
End If
End With
Next ws
Application.ScreenUpdating = True
End Sub
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It worked for me when I tested it on a workbook here - can you check when you run it if the Activeworkbook is the workbook the code is in? The code refers to ThisWorkbook so if a different workbook is active, you won't see the results in that one.

healey's suggestion will help determine which workbook the code is acting on, though you should try to avoid using .Activate (which I know healey knows!)
 

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
Hey Healey,

I tried it with the ws.activate and it crashed my excel :LOL:. Any other ideas?

Thanks!
 

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
It worked for me when I tested it on a workbook here - can you check when you run it if the Activeworkbook is the workbook the code is in? The code refers to ThisWorkbook so if a different workbook is active, you won't see the results in that one.

So I closed every other workbook and I tried it to no avail. Then I closed that workbook and made a new workbook and just put 0 or 1 in cell J1 for sheets 1-5 and nothing happened. I know my macros are enabled. I'm not sure why you're getting results and I'm not =/. I tried tweaking the range too, that didn't make a difference.
 

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
Well now it works with any new workbook, but for some reason it doesn't work with the workbook my macro creates. The value in cell J1 is 0 or a small number like the one in the original post. I'm completely lost as to why it wouldn't work.
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,744
Office Version
  1. 365
Platform
  1. Windows
Without being at your PC or seeing what you're seeing, very difficult to diagnose the reason - at least it sort of works!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,577
Messages
5,637,198
Members
416,960
Latest member
Carbon1198

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