Wizard
04-26-2007, 12:51 PM
Good Grief, this is wierd....
I have a spreadsheet where I use conditional formatting, set by a macro, to highlight instances where there is more than one occurrance of a customer code. I use VBA to set it up as part of a larger routine - the attachment just has the offending section of a considerably larger macro that is activated on the Worksheet_Change event.
It works - is supposed to work - by counting the occurances of the changed cell (Target). Formula is very simple -
"=COUNTIF($B:$B, $Bx) > 1"
where 'x' is the row number. Applies to Column B ("Code") only.
If you edit a cell (Target), and hit 'enter', the activecell moves to the next line, and the format condition formula applied to the Target cell
"=COUNTIF($B:$B, $B" & Target.Row & ") > 1"
is entered incorrectly, even though Target.Row is correct while it does it.
What you end up with is
"=COUNTIF($B:$B, $B" & Target.Row - 1 & ") > 1" :wot
Example:
You edit cell B11 & hit 'Enter'. B12 becomes the activecell, B11 is Target.
Conditional formatting is added to Target.Formula.
When that formula for that cell is read by vb property method & displayed:
MsgBox Cells(Target.Row, C).FormatConditions(1).Formula1
IMMEDIATELY AFTER the formula is added, what it shows is correct
"=COUNTIF($B:$B,$B11) > 1"
BUT if you switch to Excel, select B11 & look at the conditional formatting,
the formula is HAS THE WRONG ROW
"=COUNTIF($B:$B,$B10) > 1" :bug:
This only happens when the activecell was not on the same row as the Target cell - if you edit & hit 'Tab', it moves to cell C11 & then the formula placed in B11 is correct.
I can work around it - Re-activating the Target cell if the activecell row <> Target.row avoids the problem. I shouldn't have to work around, not for something as seemingly simple as this.
But what the bleepity-bleeping heck is causing it????
I've tried cleaning the code, moving the offending code to a module, everything I can think of. :dunno
Attached spreadsheet has examples with all the conditional formatting correct (entered manually).
Clues? Anyone? Anyone? Bueller? Bueller?
Thanks in advance....
I have a spreadsheet where I use conditional formatting, set by a macro, to highlight instances where there is more than one occurrance of a customer code. I use VBA to set it up as part of a larger routine - the attachment just has the offending section of a considerably larger macro that is activated on the Worksheet_Change event.
It works - is supposed to work - by counting the occurances of the changed cell (Target). Formula is very simple -
"=COUNTIF($B:$B, $Bx) > 1"
where 'x' is the row number. Applies to Column B ("Code") only.
If you edit a cell (Target), and hit 'enter', the activecell moves to the next line, and the format condition formula applied to the Target cell
"=COUNTIF($B:$B, $B" & Target.Row & ") > 1"
is entered incorrectly, even though Target.Row is correct while it does it.
What you end up with is
"=COUNTIF($B:$B, $B" & Target.Row - 1 & ") > 1" :wot
Example:
You edit cell B11 & hit 'Enter'. B12 becomes the activecell, B11 is Target.
Conditional formatting is added to Target.Formula.
When that formula for that cell is read by vb property method & displayed:
MsgBox Cells(Target.Row, C).FormatConditions(1).Formula1
IMMEDIATELY AFTER the formula is added, what it shows is correct
"=COUNTIF($B:$B,$B11) > 1"
BUT if you switch to Excel, select B11 & look at the conditional formatting,
the formula is HAS THE WRONG ROW
"=COUNTIF($B:$B,$B10) > 1" :bug:
This only happens when the activecell was not on the same row as the Target cell - if you edit & hit 'Tab', it moves to cell C11 & then the formula placed in B11 is correct.
I can work around it - Re-activating the Target cell if the activecell row <> Target.row avoids the problem. I shouldn't have to work around, not for something as seemingly simple as this.
But what the bleepity-bleeping heck is causing it????
I've tried cleaning the code, moving the offending code to a module, everything I can think of. :dunno
Attached spreadsheet has examples with all the conditional formatting correct (entered manually).
Clues? Anyone? Anyone? Bueller? Bueller?
Thanks in advance....