PDA

View Full Version : Solved: Conditional Formating by VBA creates its own condition



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....

Paul_Hossler
04-26-2007, 01:36 PM
My guess is that the FormatConditions is based on the cell after the Enter

This seems to work, and I added some error checking for my purposes

Paul



Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Long
Dim sFormula As String

Dim rCol As Range, rTarget As Range, rIntersect

C = 0
On Error Resume Next
C = WorksheetFunction.Match("Code", Rows(1), 0)
If C = 0 Then Exit Sub
On Error GoTo 0

Set rTarget = Target.Cells(1, 1)
Set rCol = rTarget.Parent.Columns(C)

Set rIntersect = Nothing
On Error Resume Next
Set rIntersect = Intersect(rCol, rTarget)
If rIntersect Is Nothing Then Exit Sub
On Error GoTo 0

sFormula = "=COUNTIF(" & rCol.Address(True, True) & "," & rTarget.Address(True, True) & ") > 1"


With rTarget
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:=sFormula
.FormatConditions(1).Font.FontStyle = "Bold"
.FormatConditions(1).Font.ColorIndex = 3
End With

End Sub

Bob Phillips
04-26-2007, 01:47 PM
Try this



Private Sub Worksheet_Change(ByVal Target As Range)
'------------------------------------------------------------------------------------
Dim C As Long

'If ActiveCell.Row Target.Row Then Target.Activate
C = WorksheetFunction.Match("Code", Rows(1), 0)
Cells(Target.Row, C).FormatConditions.Delete
Cells(Target.Row, C).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF($B:$B,$B" & ActiveCell.Row & ") > 1"

'MsgBox Cells(Target.Row, C).FormatConditions(1).Formula1

Cells(Target.Row, C).FormatConditions(1).Font.FontStyle = "Bold"
Cells(Target.Row, C).FormatConditions(1).Font.ColorIndex = 3

End Sub

Wizard
04-30-2007, 08:52 AM
Both solutions worked, but I'll use xld's simpler one.

Still seems odd that you can use ActiveCell.row when Target.row should do - must be an 'undocumented feature'.

Thanks guys!!:beerchug:
Wizard

Bob Phillips
04-30-2007, 09:13 AM
No, it is because Excel adjusts the CF formula relative to where the activecell is, regardless of the target cell. It can be done without Activecell, but it is more complex.