Change Tab Color Based On One time Cell Value

[Hobolord]

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!

 

[JackDanIce]

Try:

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]

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]

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]

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]

Hey Healey,

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

Thanks!

 

[healey21]

JackDanIce is correct.

 

[Hobolord]

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]

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]

Without being at your PC or seeing what you're seeing, very difficult to diagnose the reason - at least it sort of works!