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

[LabLady11]

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?

 

[Tetra201]

See if the following works for you:

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]

See if the following works for you:

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]

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?