Changing Formatting on a cell for a limited time

[amoorman]

Hi, I'm not sure how this could be done though i'm pretty sure its going to require some VBA instead of conditional formatting.

I would like cell B2 to have background colour red for 10 seconds after cell A1 is edited. So every time A1 is edited B2 will change colour for 10 seconds and then back to original formating.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then Target.Interior.ColorIndex = 3
End Sub

something similar to this i think is needed.

Many Thanks

Ashley

[royUK]

You will need to use an Ontime macro, you might be able to adapt this code.

[amoorman]

Hum, i'm afraid my knowledge of excel VBA isnt this advanced. Please can you hold my hand and help me through this.

How do i get it to run when cell b2 is edited?

Dim NextTime As Date

Sub StartFlash()
    NextTime = Now + TimeValue("00:00:10")
    With ActiveWorkbook.Styles("Flashing").Font
        If .ColorIndex = xlAutomatic Then .ColorIndex = 3
        .ColorIndex = 5 - .ColorIndex
    End With
    Application.OnTime NextTime, "StartFlash"
End Sub

Sub StopFlash()
    Application.OnTime NextTime, "StartFlash", schedule:=False
    ActiveWorkbook.Styles("Flashing").Font.ColorIndex = xlAutomatic
End Sub

[royUK]

Here's some code to try

[amoorman]

AH!! Thank you so much roy, though i do feel bad for being so little help in finding this solution.