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