Forum Discussion
Conditional Formatting within a Table
I have two rather simple conditional formatting rules defined that apply to the contents of a table. Over time, as rows are added, deleted, or moved, the rules get chopped up. So although I started with just two rules, I end up with dozens of duplicate rules, each with a different (and sometimes overlapping) 'applies to' range. I'm sure that the problem starts with Excel changing my column reference (clicking on the column label, expecting to see something like TableName[ColHeading]) into standard references ($a4:$47 for example).
I can't figure out how to make the appropriate column reference inside the conditional format 'applies to' box, that will dynamically update as the table changes, hoping then that Excel will not chop the reference up into smaller pieces.
Any thoughts?
In the past I've noticed that same phenomenon. I think you answered your own question with this:
Over time, as rows are added, deleted, or moved, the rules get chopped up.
I've learned to be very consistent, when I add rows, to put them in the center of the table, and then sort according to whatever actual sequence I want. Adding outside of the range of rows initially specified can be problematic. Deleting doesn't affect those rules. "Moving" on the other hand....kind of depends on what you mean. But as long as the rules apply equally to all cells (i.e., all rows) in a column, and I'm careful about how a add or delete, it's not been a continuing issue.
3 Replies
In the past I've noticed that same phenomenon. I think you answered your own question with this:
Over time, as rows are added, deleted, or moved, the rules get chopped up.
I've learned to be very consistent, when I add rows, to put them in the center of the table, and then sort according to whatever actual sequence I want. Adding outside of the range of rows initially specified can be problematic. Deleting doesn't affect those rules. "Moving" on the other hand....kind of depends on what you mean. But as long as the rules apply equally to all cells (i.e., all rows) in a column, and I'm careful about how a add or delete, it's not been a continuing issue.
Thanks for the insight. It helps a little. Since many of the table modifications were accomplished through macros, I'm thinking I'll change the logic to add records "1 from the bottom (or top)" rather than at the bottom or top.
I'll call this question answered, but if someone has more advice, please let me kmow.
VBA can be used to set, reset or keep rules in order. Tricky? Yes. But this works for me to consolidate.
This is brute force. But that is because I am not so clever.
Sub has 2 arguments: oTable (the table object) and colName (string) that you want to consolidate.
Sub ConsolidateConditionalFormatting(oTable, colName)
' used to keep conditional formatting from fragmenting
' survey all rows in column
' all rows in a column should end up with the same conditionals
' how and where to find "the" correct set of conditionals? ...normally it will be 1 or 2 conditionals per column...
' make all rows have the same conditionals as whichever row has the most conditionals
' Problem? yes. this is equalizing by maxCount not by conditional contents. But who cares.
' example. Row 3 may have have conditionals a, b, c. row 1000 may have a, b, d, e. all rows will end up with a, b, d, e
' is that what you want?
If oTable.ListColumns(colName).Index = 0 Then
Exit Sub ' col does not exist. might want to do error trapping.
End If
Dim ColIndex As Integer
ColIndex = oTable.ListColumns(colName).Index
' if all rows have 0 conditional formats, exit
' find the row with the most conditional formats use it to populates all other rows
' ? what if 2 rows have say 2 conditionals but they are different? oops.
Dim i, rowWithMax, iCount As Long
rowWithMax = 0
iCount = 0
For i = 1 To oTable.ListRows.count
If oTable.DataBodyRange(i, ColIndex).FormatConditions.count > iCount Then
rowWithMax = i
iCount = oTable.DataBodyRange(i, ColIndex).FormatConditions.count
End If
Next i
If iCount = 0 Then
Exit Sub ' no format conditions in any rows
End If
Dim FCO As FormatCondition
For i = 1 To oTable.DataBodyRange(rowWithMax, ColIndex).FormatConditions.count
Set FCO = oTable.DataBodyRange(rowWithMax, ColIndex).FormatConditions(i)
FCO.ModifyAppliesToRange Range(oTable.name & "[" & colName & "]")
NextEnd Sub ' ConsolidateConditionalFormatting(oTable, colName)
