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?
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?