CODEWITHSUNDEEP
vbaexcelexcel-2010conditional-formatting
Ben Millen
Ben Millen

Reputation: 11

When inserting new row, header is affected by conditional formatting

I'm making a table which requires conditional formatting. I need to insert a row above the previous row so users can enter a new job at the top. I initially used a hidden row, below which any new row was posted, however using the filters exposed this, so I found this code:

Sub insertrow()
Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
End Sub

but whenever I click the button to insert a new row it does as it's supposed to but also applies the conditional formatting to the table header.

When I go to check the conditional formatting, the values have changed to include the headers row - I'm not sure how or why?

Upvotes: 1

Views: 186

Answers (1)

pnuts
pnuts

Reputation: 59485

Conditional Formatting has a bit of a mind of its own at times. I can replicate what you see with Excel 2013 and very much doubt that is the intended behaviour. A quick fix is to just let it happen but ensure that the Table headers do not meet the CF trigger requirement. For example, if your Table has a label in E2 of Boo and you use a formula rule for that column of say =$E3>2 adjust the formula to say: 

=AND($E3>2,$E3<>"Boo")

ie let the Applies to range 'misbehave' and deal with the attempt at formatting the Table label by ensuring it does not meet the condition.

Upvotes: 1

Related Questions