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
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
Code:
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!
DoEvents
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = newColor
Loop
Start = Timer
Delay = Start + fSpeed
Do Until Timer > Delay
DoEvents
myCell.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
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