Need to change tab color when a cell is blank (ignoring the formula in the cell)

LabLady11

New Member
Joined
Oct 22, 2021
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
Hi! I'm trying to change the tab color of 'Worksheet2' when any cell in column A has data that is being pulled from Worksheet1.

Worksheet1 has a bunch of information and I'm using am 'If' formula in worksheet 2/ColumnA to automatically pull in data from worksheet 1. When the value of the 'if' formula is false the cell is empty, however VBA recognizes the formula and does not consider the cell empty, therefore the tab color wont change.

The VBA code I am currently using is as follows, which effectively works to change the tab color when all cells in worksheet2 are actually empty (no formula)

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Tab.Color = xlNone
If WorksheetFunction.CountA(Me.Cells) <> 0 Then Me.Tab.Color = RGB(255, 0, 0)
End Sub

is there a way to ignore the formula in the cells?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,728
See if the following works for you:
VBA Code:
Private Sub Worksheet_Calculate()
    With Me
        .Tab.Color = False
        If Not .Range("A:A").Find("*", .Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious) Is Nothing Then .Tab.Color = RGB(255, 0, 0)
    End With
End Sub
 

LabLady11

New Member
Joined
Oct 22, 2021
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
See if the following works for you:
VBA Code:
Private Sub Worksheet_Calculate()
    With Me
        .Tab.Color = False
        If Not .Range("A:A").Find("*", .Range("A1"), xlValues, xlWhole, xlByRows, xlPrevious) Is Nothing Then .Tab.Color = RGB(255, 0, 0)
    End With
End Sub
Unfortunately this code doesn't do anything. :(
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,728
Well, let's try to figure out what's wrong. A few questions:
1) Did you place the suggested sub in the code section of your Worksheet2?
2) Did you enable Automatic calculations in your workbook?
3) Is this correct that data in Column A of Worksheet2 are NOT being entered/changed manually but are being "pulled by formulas" from elsewhere?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,578
Messages
5,765,203
Members
425,267
Latest member
bishopc22

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