Autoformat screws up with FormatConditions?

B

bentod

I have a dynamic named range. The range has autoformatting (List 2).
When I add to the range, my code will add some conditional formatting
to the row if it needs it.

Unfortunately, once a new row is added to the range, the conditional
formatting works fine, but if I trigger autoformatting to run, my
spreadsheet looks all kinds of freaked out. Like it's in a state of
color flux.

Here's my (cheesy) code:

'-----------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

[I2].Activate
With Range([I2], [I65536].End(xlUp)).Offset(0, -8).Resize(, 10)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=
$I2=""N"""
.FormatConditions(1).Interior.ColorIndex = 10
.FormatConditions(1).Font.ColorIndex = 2
.FormatConditions.Add Type:=xlExpression, Formula1:="=
$I2=""X"""
.FormatConditions(2).Interior.ColorIndex = 19
.FormatConditions(2).Font.ColorIndex = 1
End With

Range("DataFormat").Select
Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=False,
Font:= _
False, Alignment:=False, Border:=True, Pattern:=True,
Width:=False

Application.ScreenUpdating = True

End Sub
'-------------------------------------------------------

Thanks, Todd
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top