Conditional Formatting On A Protected Table

[excelbytes]

I have a Table called Table1. The headers are in row 2, the data starts in row 3. In A1 is an Active X Combo Box that uses the names in the first column of the table ("Members") as it's list and the linked cell is behind it in A1. The worksheet has certain columns that are protected because of formulas in the table. To insert a rows, I use this code that will unprotect the sheet, add a row then re-protect it:

Sub AddRow()
ActiveSheet.Unprotect Password:="paul"
Range("A1").End(xlDown).ListObject.ListRows.Add AlwaysInsert:=False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="paul"
End Sub

After I add name(s), I need to resort it, and I use this code:

Sub SortMembers()
'
' SortMembers Macro

ActiveSheet.Unprotect Password:="paul"
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Individual Contributions").ListObjects("Table1"). _
Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Individual Contributions").ListObjects("Table1"). _
Sort.SortFields.Add2 Key:=Range("Table1[Members]"), SortOn:=xlSortOnValues _
, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Individual Contributions").ListObjects("Table1" _
).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="paul"
End Sub

I have conditional formatting that will highlight the rows of the table based on the choice of the Active X Combo Box. The range in Conditional Formatting does auto adjust when I add rows. The formula for the conditional formatting is:

=$A3=$A$1

The problem is that when I sort the table using the VBA code, the conditional formatting doesn't work unless I go into Conditional Formatting and Manage the formula by just touching it. Everything does work normally when I add rows and sort it manually when the sheet is not protected. Is there something in my code that is causing this problem?

 

[excelbytes]

Here's an update on this issue: I have three macros. One adds a row to the bottom of the list, one deletes a selected row, and one sorts the list in A to Z order.

I have conditional formatting that will highlight the rows of the table based on the choice of the Active X Combo Box. The range in Conditional Formatting does auto adjust when I add rows. The formula for the conditional formatting is:

=$A3=$A$1

The Conditional Formatting works after I delete a row or sort the list, but doesn't work after I add a row, even though the Conditional Formatting range does auto adjust.

Here are the codes for the three macros:

Sub DeleteRow()
'
' DeleteRow Macro
'
ActiveSheet.Unprotect Password:="paul"
Selection.Delete Shift:=xlUp
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="paul"

End Sub

Sub AddRow()
ActiveSheet.Unprotect Password:="paul"
Range("A1").End(xlDown).ListObject.ListRows.Add AlwaysInsert:=False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="paul"
End Sub

Sub SortMembers()
'
' SortMembers Macro

ActiveSheet.Unprotect Password:="paul"
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Individual Contributions").ListObjects("Table1"). _
Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Individual Contributions").ListObjects("Table1"). _
Sort.SortFields.Add2 Key:=Range("Table1[Members]"), SortOn:=xlSortOnValues _
, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Individual Contributions").ListObjects("Table1" _
).Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="paul"
End Sub