Code to make cell flash in Excel needed :RESOLVED

[Excel Guru]

Hello all! I am new to VBforum and need some help. I am a newbee with VBA and VB. I need some help with some code. I want to make the background of a specific cell i.e. G39 Flash red, when the value entered is below a established amount specified in E39. I just need something I can build on. Any help would greatly be appreciated. Thanks

[RobDog888]

Welcome to the Forums Excel Guru!

Do you need it to flash or just change the background color? This will just
change the background color to red if the value entered in E39 is less then
100.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("E39").Value < 100 Then
        With Range("G39").Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    Else
        With Range("G39").Interior
            .ColorIndex = 0
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    End If
End Sub

VB/Office Guru™

[RobDog888]

Here is better code that fixes the border coloring issue. Place this behind Sheet1 for ex.
or we can change the code to run from a module on all sheets.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("E39").Value < 100 Then
        With Range("G39").Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    Else
        With Range("G39")
            .Interior.ColorIndex = 0
            .Interior.Pattern = xlSolid
            .Interior.PatternColorIndex = xlAutomatic
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
        End With
    End If
End Sub

[Excel Guru]

Thanks RobDog888. The first example worked good but can I get it to flash? Your help is greatly appreciated and thanks for the quick response.

[RobDog888]

I suppose so.

Insert paycheck here -->

How long do you want it to continue flashing?

[RobDog888]

This will make it flash for 30 seconds with an interval of 500 miliseconds.

Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Start
    Dim FlashDuration
    FlashDuration = 30000 '30 seconds
    If Range("E39").Value < 100 Then
        Start = Timer    ' Set start time.
        Do While Timer < Start + FlashDuration
            DoEvents    ' Yield to other processes.
            Sleep 500
            FlashMe
        Loop
        With Range("G39").Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    Else
        With Range("G39")
            .Interior.ColorIndex = 0
            .Interior.Pattern = xlSolid
            .Interior.PatternColorIndex = xlAutomatic
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
        End With
    End If
End Sub
 
Private Sub FlashMe()
    Static bOn As Boolean
    If bOn = True Then
        bOn = False
        With Range("G39")
            .Interior.ColorIndex = 0
            .Interior.Pattern = xlSolid
            .Interior.PatternColorIndex = xlAutomatic
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
        End With
    Else
        bOn = True
        With Range("G39").Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    End If
End Sub

[Excel Guru]

Thanks again RobDog888. Just what I needed Thanks for taking the time to answer my post in a timely manner and with a quick and accurate response. You are a true GURU

[RobDog888]

Here are your new changes you asked about.

Private Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Start
    Dim FlashDuration
    FlashDuration = 15 '30 seconds
    If Range("E39").Value > Range("G39").Value Then
        Start = Timer    ' Set start time.
        Do While Timer < Start + FlashDuration
            DoEvents    ' Yield to other processes.
            Sleep 250
            FlashMe
        Loop
        With Range("G39").Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    Else
        With Range("G39")
            .Interior.ColorIndex = 10
            .Interior.Pattern = xlSolid
            .Interior.PatternColorIndex = 10 'xlAutomatic
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders.ColorIndex = 10
        End With
    End If
End Sub
 
Private Sub FlashMe()
    Static bOn As Boolean
    If bOn = True Then
        bOn = False
        With Range("G39")
            .Interior.ColorIndex = 0
            .Interior.Pattern = xlSolid
            .Interior.PatternColorIndex = xlAutomatic
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
        End With
    Else
        bOn = True
        With Range("G39").Interior
            .ColorIndex = 3
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
        End With
    End If
End Sub

[esmaeel]

Dear Sir/Madam,
I need to flash a cell for example if cell C36 greater than 100 with red/pink color and if C36 is less or equal 100 ,the cell flash yellow/orange color.
May u help me?