Help with VBA............Again.

[sonicscooter]

Yesterday Joel was kind enough to help me by sending this as a better way of
writng what i had done, it works perfectly, but is there a way that i can use
it to access several ranges, ie instead of just using say A1:A10 > B1:B10, by
making, For RowCount = 1 To 10, can i have BB1:A10 > B1:B10
BC1:A10 > C1:B10
BD:A10 > D1:B10
Many thanks.

Private Sub Worksheet_Calculate()

 

[joel]

You description wan't very clearr but I asumed you want every cell to blink
together rather the one at a time. I create a UNION of cells and blinked the
union

Private Sub Worksheet_Calculate()
Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed
Dim NuberGreaterThan As Integer
Dim BlinkRange As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row

''Create a union of cells to blink
First = True
For RowCount = 1 To LastRow
If Range("A" & RowCount) > Range("B" & RowCount) Then
Range("A" & RowCount).Copy
Range("B" & RowCount).PasteSpecial _
Paste:=xlPasteValues
If First = True Then
Set BlinkRange = Range("G" & RowCount)
First = False
Else
Set BlinkRange = Application.Union(BlinkRange, Range("G" &
RowCount))
End If
End If
Next RowCount

'found at least pair of cells that was true
If First = False Then
Beep
newColor = 42
fSpeed = 0.4
Do Until x = 10
DoEvents
Start = Timer
Delay = Start + fSpeed

Do Until Timer > Delay
DoEvents
BlinkRange.Interior.ColorIndex = newColor
Loop

Start = Timer
Delay = Start + fSpeed

Do Until Timer > Delay
DoEvents
BlinkRange.Interior.ColorIndex = xlNone
Loop
x = x + 1
Loop
End If
End Sub

 

[sonicscooter]

Hi Joel, sorry if it wasn't clear, the code you sent me yesterday works fine,
ie blinking each cell when A>B etc, but it works for cells in a range in one
column ie A1 then it counts rows to lets say A10, but what do i need to
change if i want to monitor column A1 to A10 and column B1 to B10 and so on.
In total i have 15 columns to monitor with 44 cells in each ie A1:A44, then
B1:B44 and so on.

It works just the way i want with each cell blinking individualy when a
value is exceeded...

Thanks for your help. Shane.

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow

 

[joel]

1) Are you comparing A with B then A with C. Or comparing A with B then C
with D?
2) Do you always want to blink column G (column 7) or differrent columns?
If yo have 10 columns A - J then why are you blinking G in the middle?
3) Do yo want to blink one at a time or in groups like the code I provieded
this morning.

 

[sonicscooter]

Hi, im wanting to compare individual cells in column A to individual cells in
B ie compare A1 to B1 down the list to row 44, like Number 1 in your question
list......i've changed G to A so that after B is pasted to A, A's background
flashes, which works fine,... so i want to compare individual cells in column
A with individual cells in column B, then compare individual cells in column
C with individual cells in column D and so on to a total of 15 columns, all
with rows 1 to 44....

Sorry i haven't made it clear..Shane.

 

[joel]

Here is the code that blinks on cell at a time. If you want to go across
columns then down a row switch the two FOR loops (RowCount and ColCount).

Private Sub Worksheet_Calculate()

Dim newColor As Integer
Dim myCell As Range
Dim x As Integer
Dim fSpeed

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For ColCount = 1 To 30 Step 2
For RowCount = 1 To LastRow

If Cells(RowCount, ColCount) > Cells(RowCount, ColCount + 1) Then
Beep
Cells(RowCount, ColCount).Copy
Cells(RowCount, ColCount + 1).PasteSpecial _
Paste:=xlPasteValues

Set myCell = Cells(RowCount, ColCount)
newColor = 42
fSpeed = 0.4
Do Until x = 10
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
End If
Next RowCount
Next ColCount
End Sub

 

[sonicscooter]

Thanks Joel, i've given it a quick go and it seems to solve the problem, just
out of interest, with, For ColCount = 1 To 30 Step 2, which looks in a A to B
direction, is it possible to go from E to F step 2 then A to B, ie checking
columns from right to left?

Thanks again, im not sure if you realise how much you are helping people
save time and headaches, i felt like giving up on this
problem....Cheers..Shane.

 

[sonicscooter]

I changed some things around and now it works from right to left.....BRILLIANT.