The Flasher: Show A Cell, Cells, Or Merged Cells With A Warning Colour

This is a common theme: your sheet contains a cell for a file location, some macro runs with the value, and fails because the file address is invalid.

Fine, we've all written error-handlers for that, and we can write informative error messages telling the user what's gone wrong, and what to do next.

And we all *do* do this... Right?

As useful bit of user interface work is to flash up the range with the bad data, changing the background colour to red, and back again, two or three times. It's an easy bit of code to write, until you run into ranges containing merged cells - which are a nuisance, but an occasional necessity - because the range.interior.color property fails silently on merged cells.

We also have a second consideration: interior.color sometimes returns zero for cells with no interior colour. This is embarrassing when you've read the original colour as zero and 'restore' the range to that after flashing and ringing bells.

This is the code to flash a cell in red, three times (the default settings for 'WarningFlash'):

WarningFlash rngFileControl.Cells(iRow, iCol)

And this is what the code looks like to flash the cell (or merged cells) behind a command button, twice, in a tasteful yellow colour:

WarningFlash rngFileControl.Rows.Worksheet.OLEObjects("cmdSelectFiles").TopLeftCell, &HFFFF, 2

Note that we select the top-left cell of the range: my code for merged ranges only works if you specify the whole of the merge, or the first cell in the merge. And yes, I often put COM control buttons in merged cells: a well-formatted button with a 16*16 icon is twice the height of a standard Excel row, and I prefer to have the control in a merged single cell because 'move and size with cells' works badly if the control spans more than one cell.

Coding Note: best-practice would be to save your favourite colours as constants, or to use the built-in VBA colour constant for yellow: VBA.ColorConstants.vbYellow

So, without further ado, the source for the WarningFlash subroutine:

Public Sub WarningFlash(FlashRange As Excel.Range, _ 
                         Optional WarningColor As Long = &HFF, _ 
                         Optional FlashCount As Integer = 3)
 
Dim lngPriorColor As Long
Dim i As Integer
 
Dim rng As Excel.Range
 
    ' This unwieldly syntax works on ranges of merged cells, if you specify the top left cell
    Set rng = FlashRange.Areas(1).Worksheet.Range(FlashRange.Areas(1).Address)
 
    lngPriorColor = rng.Interior.Color
    If lngPriorColor = 0 Then Exit Sub
 
    For i = 1 To FlashCount
        rng.Interior.Color = WarningColor
        Sleep 500
        rng.Interior.Color = lngPriorColor
        Sleep 500
    Next i
 
End Sub

And now for the difficult bit: I'm using 'sleep', because Application.Wait only works in whole seconds. That API declaration needs to work in all Windows versions, and in all versions of VBA:

 
#If VBA7 And Win64 Then    ' 64 bit Excel under 64-bit windows
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongLong)
#ElseIf VBA7 Then     ' 64 bit Excel in all environments
    Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else    ' 32 bit Excel
    Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

Let me know how you get on: or, better still, post your own solutions.