| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with VBA
Hi, this works for one cell, but how do i use this for several hundred cells
independently of each other, ie monitor cells, it compares A to B, if A is higher than B, it beeps, G1 flashes slowly, and copies A to B, input to A is from a DDE link from another program, so if A then changes again, higher than its previous value, the sequence starts again, so you end up with B being the maximum value of the time period that i monitored the event or if a certain value is reached i can have a message appear in B1 to alert me to do somthing, but although it works for one cell, i wish to compare, A1 B1 A2 B2 A3 B3 and so on, do i have to change it from, Private Sub Worksheet_Calculate() to something else to asighn it to each set of cells ? or copy this dozens of times but change the Private Sub Worksheet_Calculate(). im completely lost as to how to do it. Thanks for any help. Private Sub Worksheet_Calculate() If Range("A1") Range("B1") Then Beep Range("A1").Copy If Range("A1") Range("B1") Then Range("B1").PasteSpecial Paste:=xlPasteValues Dim newColor As Integer Dim myCell As Range Dim x As Integer Dim fSpeed Set myCell = Range("G1") 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 End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with VBA
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 RowCount = 1 To LastRow If Range("A" & RowCount) Range("B" & RowCount) Then Beep Range("A" & RowCount).Copy Range("B" & RowCount).PasteSpecial _ Paste:=xlPasteValues Set myCell = Range("G" & RowCount) 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 End Sub "sonicscooter" wrote: Hi, this works for one cell, but how do i use this for several hundred cells independently of each other, ie monitor cells, it compares A to B, if A is higher than B, it beeps, G1 flashes slowly, and copies A to B, input to A is from a DDE link from another program, so if A then changes again, higher than its previous value, the sequence starts again, so you end up with B being the maximum value of the time period that i monitored the event or if a certain value is reached i can have a message appear in B1 to alert me to do somthing, but although it works for one cell, i wish to compare, A1 B1 A2 B2 A3 B3 and so on, do i have to change it from, Private Sub Worksheet_Calculate() to something else to asighn it to each set of cells ? or copy this dozens of times but change the Private Sub Worksheet_Calculate(). im completely lost as to how to do it. Thanks for any help. Private Sub Worksheet_Calculate() If Range("A1") Range("B1") Then Beep Range("A1").Copy If Range("A1") Range("B1") Then Range("B1").PasteSpecial Paste:=xlPasteValues Dim newColor As Integer Dim myCell As Range Dim x As Integer Dim fSpeed Set myCell = Range("G1") 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 End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with VBA
Joel, thankyou for the very fast responce, i was thinking i would have to
make my version about half a mile long with all the cells i needed to monitor, and then end up with "procedure too long" etc i've never used RowCount as yet, so i will try your version and mess with it, so far i works really well. Thankyou again....Cheers. "joel" wrote: 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 RowCount = 1 To LastRow If Range("A" & RowCount) Range("B" & RowCount) Then Beep Range("A" & RowCount).Copy Range("B" & RowCount).PasteSpecial _ Paste:=xlPasteValues Set myCell = Range("G" & RowCount) 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 End Sub "sonicscooter" wrote: Hi, this works for one cell, but how do i use this for several hundred cells independently of each other, ie monitor cells, it compares A to B, if A is higher than B, it beeps, G1 flashes slowly, and copies A to B, input to A is from a DDE link from another program, so if A then changes again, higher than its previous value, the sequence starts again, so you end up with B being the maximum value of the time period that i monitored the event or if a certain value is reached i can have a message appear in B1 to alert me to do somthing, but although it works for one cell, i wish to compare, A1 B1 A2 B2 A3 B3 and so on, do i have to change it from, Private Sub Worksheet_Calculate() to something else to asighn it to each set of cells ? or copy this dozens of times but change the Private Sub Worksheet_Calculate(). im completely lost as to how to do it. Thanks for any help. Private Sub Worksheet_Calculate() If Range("A1") Range("B1") Then Beep Range("A1").Copy If Range("A1") Range("B1") Then Range("B1").PasteSpecial Paste:=xlPasteValues Dim newColor As Integer Dim myCell As Range Dim x As Integer Dim fSpeed Set myCell = Range("G1") 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 End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with VBA
Hi Joel, i've adjusted it to my own needs and it works spot on, thankyou again.
"sonicscooter" wrote: Joel, thankyou for the very fast responce, i was thinking i would have to make my version about half a mile long with all the cells i needed to monitor, and then end up with "procedure too long" etc i've never used RowCount as yet, so i will try your version and mess with it, so far i works really well. Thankyou again....Cheers. "joel" wrote: 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 RowCount = 1 To LastRow If Range("A" & RowCount) Range("B" & RowCount) Then Beep Range("A" & RowCount).Copy Range("B" & RowCount).PasteSpecial _ Paste:=xlPasteValues Set myCell = Range("G" & RowCount) 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 End Sub "sonicscooter" wrote: Hi, this works for one cell, but how do i use this for several hundred cells independently of each other, ie monitor cells, it compares A to B, if A is higher than B, it beeps, G1 flashes slowly, and copies A to B, input to A is from a DDE link from another program, so if A then changes again, higher than its previous value, the sequence starts again, so you end up with B being the maximum value of the time period that i monitored the event or if a certain value is reached i can have a message appear in B1 to alert me to do somthing, but although it works for one cell, i wish to compare, A1 B1 A2 B2 A3 B3 and so on, do i have to change it from, Private Sub Worksheet_Calculate() to something else to asighn it to each set of cells ? or copy this dozens of times but change the Private Sub Worksheet_Calculate(). im completely lost as to how to do it. Thanks for any help. Private Sub Worksheet_Calculate() If Range("A1") Range("B1") Then Beep Range("A1").Copy If Range("A1") Range("B1") Then Range("B1").PasteSpecial Paste:=xlPasteValues Dim newColor As Integer Dim myCell As Range Dim x As Integer Dim fSpeed Set myCell = Range("G1") 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 End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
