help with VBA


New member
Jul 7, 2014
Reaction score
Dear All

I have managed to write the following code with the help from this forum. But I am stuck in one bit ad I am new to VBA and will appreciate your help.

Basically, this code will look at my customers and if they have spent more than 1000 pounds and agreed to receive email and its been more than 30 days since their first purchase then the cell where their surname (column B) is will start flashing for two seconds and the date when I get the reminder is recorded in column J .

When I run the code everything works fine and the dates are entered correctly, however the only cell that flashes in B5 even though cells B7 and B9 should flash as well since they meet my If criteria.

I think the line Set myCell= .Cells(I, 2) is not working properly as it doesn't move on to the next cell to check whether it needs to flash or not.

I have attached my code and any help is much appreciate it as I need the code to make all the cells that meet my criteria to flash rather than just the first one

Private Sub CommandButton1_Click()
    Dim i As Long
 'Make cell range Background color, flash x times, x fast, in x color,
 'when Ctrl-a is pressed, you must set this macro option.
 Dim newColor As Integer
 Dim myCell As Range
 Dim x As Integer
 Dim fSpeed

    With Sheets("Sheet1")
        ' if 30 days have passed and customer has spent more than 1000 and want to recive email
        For i = 5 To .Range("A" & Rows.Count).End(xlUp).Row
            If DateDiff("d", .Cells(i, 10).Value, Now) >= 30 And .Cells(i, 11).Value = "y" And .Cells(i, 8).Value >= 1000 Then
                If DateDiff("d", .Cells(i, 9).Value, Now) >= 30 Then
                    'Make this cell range background flash!
                     Set myCell = .Cells(i, 2)
                    Application.DisplayStatusBar = True
                    Application.StatusBar = "... Select Cell to Stop and Edit or Wait for Flashing to Stop! "

                    'Make cell background flash to this color!
                     'Black 25, Magenta 26, Yellow 27, Cyan 28, Violet 29, Dark Red 30,
                     'Teal 31, Blue 32, White 2, Red 3, Light Blue 41, Dark Blue 11,
                     'Gray-50% 16, Gray-25% 15, Bright Cyan 8.
                     newColor = 41
                     'Make the cell range flash fast: 0.01 to slow: 0.99
                     fSpeed = 0.2
                     'Make cell flash, this many times!
                     Do Until x = 2
                     'Run loop!
                     Start = Timer
                     Delay = Start + fSpeed
                     Do Until Timer > Delay
                     myCell.Interior.ColorIndex = newColor
                     Start = Timer
                     Delay = Start + fSpeed
                     Do Until Timer > Delay
                     myCell.Interior.ColorIndex = xlNone
                     x = x + 1
                     Application.StatusBar = False
                     Application.DisplayStatusBar = Application.DisplayStatusBar
                    .Cells(i, 10).Value = Format(Now, "dd/mm/yyyy")
                    End If
                End If
            Next i
            End With
        End Sub