CODEWITHSUNDEEP
vbaexcel
Mike Barnes
Mike Barnes

Reputation: 4305

Call a sub when a cell is a certain value

I would like to call my sub that causes a cell to blink between red and white if the value of cell N63 = 0. So in other words, if cell D70 = 0 then StartBlinking else StopBlinking.

Here is the Bliking sub

Option Explicit

Public NextBlink As Double
'The cell that you want to blink
Public Const BlinkCell As String = "Sheet1!D70"

'Start blinking
Public Sub StartBlinking()
    Application.Goto Range("A1"), 1
    'If the color is red, change the color and text to white
    If Range(BlinkCell).Interior.ColorIndex = 3 Then
        Range(BlinkCell).Interior.ColorIndex = 0
        Range(BlinkCell).Value = "White"
    'If the color is white, change the color and text to red
    Else
        Range(BlinkCell).Interior.ColorIndex = 3
        Range(BlinkCell).Value = "Red"
    End If
    'Wait one second before changing the color again
    NextBlink = Now + TimeSerial(0, 0, 1)
    Application.OnTime NextBlink, "StartBlinking", , True
End Sub

'Stop blkinking
Public Sub StopBlinking()
    'Set color to white
    Range(BlinkCell).Interior.ColorIndex = 0
    'Clear the value in the cell
    'Range(BlinkCell).ClearContents
    On Error Resume Next
    Application.OnTime NextBlink, "StartBlinking", , False
    Err.Clear
End Sub

Here is my if that does not work:

    If Worksheets("Sheet1").Range("N63").Value = 0 Then
        Call StartBlink()
    Else
        Call StopBlink()
    End If

How do I call these two subs?

Upvotes: 1

Views: 1894

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169494

By taking advantage of the Worksheet_Change event you can run some code for each change to a particular range. Below is an example tailored to your use-case:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells contains the cells that will
    ' cause an alert when they are changed.
    Set KeyCells = Range("D70")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        If KeyCells.Value = 0 Then 
            StartBlinking
        Else 
            StopBlinking
        End If
    End If
End Sub

Upvotes: 2

Related Questions