Solved: Code is broken

[Sir Babydum GBE]

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?

[mvidas]

BD,

1: Why is it sticking on "Target"?
As you have it, Target is used as a Range object, like you'd find for a Worksheet_Change event. As it is currently, you could replace 'Target' with 'adfboerbalkejfghawergaewr' and it would have the same effect, as Target is not dimensioned or set as anything.

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?
I'm guessing you were referring to the Worksheet_Change event that wasn't getting triggered .. unfortunately that event tells you what changed, _Calculate doesn't. If _Change doesnt work for you, you'll have to check them all.

By the way, unless you're trying to set a range on one sheet based on the same address as another sheet, the following two snippets are identical:Range(Target.Address)
TargetYou don't need to set the range of a range's address when you can just refer to that range directly (using 'Target' in that example) If Rng1 Is Nothing Then
' Set Rng1 = Range(Target.Address)
Set Rng1 = Target
Else
' Set Rng1 = Union(Range(Target.Address), Rng1)
Set Rng1 = Union(Target, Rng1)
End IfMatt

[Sir Babydum GBE]

Looks like World Peace is still in the offing...

Thanks Matt, I understood what you said (Sort of) so I can't use calculate because it doesn't take account of what the selection is - and i cant use selection change because 97's macros aren't triggered by drop-down validation.

So i remembered an article I put into the kb about going back to the starting cell after code finished, and borrowed a bit from that to modify Jake's fab conditional formatting code - and I came up with this:

Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit



Private Sub Worksheet_Calculate()

Dim Cell As Range
Dim Rng1 As Range
Set Rng1 = Selection

On Error Resume Next
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Selection
Else
'Set Rng1 = Union(Range("B5:IQ50"), Rng1)
Set Rng1 = Selection
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "N1"
Cell.Interior.ColorIndex = 40
Cell.Font.ColorIndex = xlColorIndexAutomatic


And it works like a dream.

Nice bit of coaching there, Matt.

Sir BD.

[mvidas]

Glad to help, though it looks like you've solved it yourself :)
I can't say I've ever used xl97, but I'm aware of it's many limitations, but you say in xl97 validation won't trigger the _SelectionChange event. Have you tried using just the _Change event (which does have a 'Target' argument)?