Run Worksheet code automatically

[LabLady11]

Hello,

I am running the code below in a worksheet but have to double click on the cell in order for it to run. Can this run automatically?

Private Sub Worksheet_Change(ByVal Target As Range)
MyVal = Range("A1").Text


With ActiveSheet.Tab
Select Case MyVal
Case "1"
.Color = vbRed
Case "0"
.ColorIndex = xlColorIndexNone
Case Else
.ColorIndex = xlColorIndexNone
End Select
End With
End Sub

 

[Fluff]

Does A1 have a formula in it?

 

[LabLady11]

Does A1 have a formula in it?

sure does!

=IF(COUNTIF(pmvalue,1),1,0)

PMvalue is a range on another sheet

 

[Fluff]

In that case try

Private Sub Worksheet_Calculate()
   Select Case Range("A1").Value
      Case 1
         Me.Tab.Color = vbRed
      Case Else
         Me.Tab.ColorIndex = xlColorIndexNone
   End Select
End Sub

Although this will run when any cell in the sheet is calculated.

 

[LabLady11]

In that case try

Private Sub Worksheet_Calculate()
   Select Case Range("A1").Value
      Case 1
         Me.Tab.Color = vbRed
      Case Else
         Me.Tab.ColorIndex = xlColorIndexNone
   End Select
End Sub

Although this will run when any cell in the sheet is calculated.

THANK YOU SO MUCH!!! IT WORKS

 

[Fluff]

You're welcome & thanks for the feedback.