Sir Babydum GBE
06-12-2006, 07:50 AM
Hi,
In my new "World_Peace()" macro, I am a little stuck over a simple thing - at least I'm sure it's simple for you.
Private Sub Worksheet_Calculate()
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveCell
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case "N1"
Cell.Interior.ColorIndex = 40
Cell.Font.ColorIndex = xlColorIndexAutomatic
Using xl97 the validation doesn't trigger the code, so i changed the sub to a Worksheet_Calculate, and put a counta in a hidden cell so that a user input triggers a calculation.
It's getting stuck on the word "Target" saying it's a "Compile Error - variable not defined".
I need it to only execute the code on the cell or cells that have just been altered - whereas it was looking at the entire sheet and taking forever. I guess my "ActiveCell" line is fine for a single cell change, but if a user highlights a range, enters text and presses CTRL + Enter, how do i get it to work on all the affected cells.
So to recap:
1: Why is it sticking on "Target"?
2: How do i get it to do it's business only on cells that have just been changed, without looking through the whole massive sheet?
You know I love you... right?
In my new "World_Peace()" macro, I am a little stuck over a simple thing - at least I'm sure it's simple for you.
Private Sub Worksheet_Calculate()
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveCell
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case "N1"
Cell.Interior.ColorIndex = 40
Cell.Font.ColorIndex = xlColorIndexAutomatic
Using xl97 the validation doesn't trigger the code, so i changed the sub to a Worksheet_Calculate, and put a counta in a hidden cell so that a user input triggers a calculation.
It's getting stuck on the word "Target" saying it's a "Compile Error - variable not defined".
I need it to only execute the code on the cell or cells that have just been altered - whereas it was looking at the entire sheet and taking forever. I guess my "ActiveCell" line is fine for a single cell change, but if a user highlights a range, enters text and presses CTRL + Enter, how do i get it to work on all the affected cells.
So to recap:
1: Why is it sticking on "Target"?
2: How do i get it to do it's business only on cells that have just been changed, without looking through the whole massive sheet?
You know I love you... right?