Highlight cell for one second like stockmarket software

[eros]

Hi all,

Is there a way to highlight a cell for one second, when the cell value is updated by a vba function.

The challenge here is that the cell value is calculated in a vba function, not in a sub; therefore, direct access to the cell is not permitted, (as far as I know)

The Cell is expected to be highlighted in yellow and after a second back to normal.

Many thanks.

 

[gaj104]

If I'm understanding right:

Put this in a standard . Edit the address of the cell that you want to flash.

Function MyCell() As Range
Set MyCell = Sheets("Sheet1").Range("B3") 'Cell that will flash. Edit as required
End Function

Sub FlashMe()

With MyCell

    If .Interior.ColorIndex = 6 Then
        .Interior.ColorIndex = xlNone
    Else
        .Interior.ColorIndex = 6
    End If

End With

End Sub

Then put this in the sheet code that contains that cell

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address(0, 0) = MyCell.Address(0, 0) Then
    Call FlashMe
    
    Application.OnTime Now + TimeValue("00:00:01"), "FlashMe"
End If

End Sub

If VBA code changes the cell, the worksheet change will trigger to give a flashing cell and then return it back to blank.

Hope it helps

 

[eros]

Thank you very much for your prompt return @gaj104.

If the cells to highlight are in fact a 200 row by 3 column table, am I supposed to define a seperate sub for each cell in this table?

I hope, I am not...

 

[eros]

... Moreover, the worksheet is very busy gathering DDE data from an external server; therefore, using Worksheet_Change event could make Excel get very busy updating the sheet, I am afraid...

Sorry for not making your life easier...

 

[gaj104]

Ok, this still uses worksheet change, but uses a predefined range A1:C200 in this example. So anytime a cell is changed via the vba code it will flash if it is in that range.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim strR As String

If Not Application.Intersect(Target, Me.Range("A1:C200")) Is Nothing Then

    strR = Target.Address(0, 0)

    Call FlashMe(strR)
    Application.OnTime Now + TimeValue("00:00:01"), "'FlashMe """ & strR & """'"

End If

End Sub

Then in the standard module this is all you need.

Sub FlashMe(strR As String)

Dim myCell As Range
Set myCell = Sheets("Sheet1").Range(strR)

With myCell

    If .Interior.ColorIndex = 6 Then
        .Interior.ColorIndex = xlNone
    Else
        .Interior.ColorIndex = 6
    End If

End With

End Sub

See how it performs.

 

[eros]

Two questions/concerns:

1) Am I wrong to thing that Worksheet_Change event is called when a cell in a worksheet is manually changed, not when updated by a DDE link etc. All of my cell data comes from an external server with DDE links as I don't type anything manually, and I am worried if it will properly trigger the worksheet_change event.

If you think the solution to this is to use Worksheet_Calculate, I am more concerned then as I am making Excel extremely busy, getting the external data continuously, calculating it, sorting 3x200 table, putting each of 3x200 cells a trigger to check if a second has passed. I am afraid Excel would just stop responding.

2) A1:C200 in my case is an automatically-sorted table. And based on several and continuous calculations it is sorted automatically. That means a cell when highlighted by your sub being in row 55, column 3, may not reside on that specific address after 1 second - when it needs to be de-highlighted. Does not this cause any inconsistency?

Many thanks for your kind assistance.

 

[eros]

By the way,

I cannot try your solution before the actual market is open, therefore, your solution might be perfectly ok, but currently I am not able to test it.

I am just thinking if you could suggest a better solution considering likely performance issues I have just mentioned.

 

[eros]

I realize my both concerns do still apply.

Any suggestions?