Code to Change Tab Color if Data Exists

[jscalem]

Is there a code that will change the color of a tab if there is data on the worksheet?

[6StringJazzer]

The following code placed in the code module for a worksheet will change the tab color to red once data has been entered on the worksheet. It does not change it back again if the sheet subsequently has all data deleted.

Please Login or Register  to view this content.

If you want code in one place that does it for all worksheets, it's quite a bit trickier but I think it's possible.

[jscalem]

There are a total of 12 tabs in the workbook. I need 8 of them (soon to be 7) to change color if there is data on them.

[6StringJazzer]

My best suggestion is to replicate the above code in each of the 8 (soon to be 7) sheets that you want to have the tab change color when data is added. You do not have to change the code except if you want the tabs to be different colors.

I experimented with using a Class to handle events from any sheet but have not gotten it to work yet. Because you only want some of the sheets to have that behavior, that would be a problematic solution anyway.

[JOHN H. DAVIS]

Another option:

Please Login or Register  to view this content.

This will remove the Tab Color as well and operates on all Sheets within the workbook. If there are sheets you don't want included you need to place a line at the beginning. In the sample code above it excludes Sheet3

[6StringJazzer]

This will remove the Tab Color as well and operates on all Sheets within the workbook.

Excellent suggestion, I had forgotten about that event!

[jscalem]

Another option:

Please Login or Register  to view this content.

This will remove the Tab Color as well and operates on all Sheets within the workbook. If there are sheets you don't want included you need to place a line at the beginning. In the sample code above it excludes Sheet3

I hit Alt+F11 and put this code into a new module however it is not working.

[jscalem]

The tab names I need excluded would be the below.

Client List
Combined
Correct File
Research

[fredlo2010]

I hit Alt+F11 and put this code into a new module however it is not working.

Hi,

This is a workbook event and has to be placed under "ThisWorkbook" module

I have modified the code provided by John to exclude the specified sheets.

Thanks

Please Login or Register  to view this content.

[6StringJazzer]

I am getting an error on this line if the sheet is not completely blank. Using UsedRange in a comparison is failing.

Please Login or Register  to view this content.

I was able to get this modification to work.

Please Login or Register  to view this content.

[fredlo2010]

I am getting an error on this line if the sheet is not completely blank. Using UsedRange in a comparison is failing.

Please Login or Register  to view this content.

I was able to get this modification to work.

Please Login or Register  to view this content.

I just tested it and I dont get an error but the tab color does not change back to none. Its strange :?

[Winon]

It works well for me.

[fredlo2010]

maybe its time for me to close the application completely and delete temp files to clean Excel garbage

[6StringJazzer]

There are multiple solutions posted close together with multiple members offering commentary but we can't tell who is responding to what.

Let's wait for jscalem to review what's here and post back to tell us whether any of this is solving his problem.