VBA for conditional formatting, but not applied dynamically

Topic

New Reply

Dear loungers,

Since Excel doesn’t maintain conditional formatting as I would like – once new rows and columns are added it becomes unmanageable – I have got into the habit of having a bit of VBA to reset the conditional formatting rules. This works well in that on “pressing the button” the sheet’s formatting is as I want it and the rules are set. What then doesn’t work is that when editing the sheet the formatting doesn’t get applied properly, even though the rules look right; so I have to “press the button” each time to apply the formatting.

Well they don’t quite look right. The range isn’t always populated when I inspect the rules using Manage Rules. What is odd is that the range will sometimes populate and sometimes not as I move the slider on the right of my rules, so this may be a red herring.

The VBA code for each rules looks something like this in each case:

Code:

        With .Range(“a6:r1001″)             ‘ TEAL Header line – with a number for the action and a zero as the line within the action
            .Activate
            .FormatConditions.Add xlExpression, Formula1:=”=AND(ISNUMBER($a6),$b6=0)”
            .FormatConditions(5).StopIfTrue = True
            .FormatConditions(5).Font.ThemeColor = xlThemeColorDark1
            .FormatConditions(5).Borders.LineStyle = xlContinuous
            .FormatConditions(5).Borders.TintAndShade = -0.249946592608417
            .FormatConditions(5).Borders(xlLeft).ColorIndex = 31
            .FormatConditions(5).Borders(xlRight).ColorIndex = 31
            .FormatConditions(5).Borders(xlBottom).ColorIndex = 31
            .FormatConditions(5).Borders(xlTop).ColorIndex = 1
            .FormatConditions(5).Interior.Color = 10066176
        End With

I also attach the sheet. Can I do something better?

Thank you…………….. liz

Reply | Quote

Replies

I am not sure what the problem is exactly. How are you inserting new rows and columns? When I insert new rows and/or columns the conditional formatting expands to include the new region. I can enter new cell entries and it maintains the formatting from the insert rows/columns.

If I want to copy already existing cells, I can copy and paste-values into the cells (to maintain that formatting). Are you inserting new cells but trying to keep their formatting instead of using the existing formatting? This would become cumbersome, as you are telling excel to split the existing range and then add a new range of formats…

Steve

Reply | Quote

Hi Steve,

The original problem is that, depending how things are inserted or amended, you can end up with a huge number of conditional format rules that amount to the right thing – effected by a number of rules applied to several ranges that together might be the original range expanded or reduced as you intended. This means the sheet will behave as you expect until it doesn’t and the rules, by then, have multiplied to make maintenance impossible. In the attached spreadsheet I copied line 72 and pasted it to reused it in line 75. If you check the rules for the sheet they have started to disaggregate so we now have many rules.

So I come back to the “real” problem which is how to make the VBA “stick” once applied, it behaved correctly, the rules look correct but they do not get applied as I change data I have to rerun the VBA.

Thank you

liz

Reply | Quote

In the attached spreadsheet I copied line 72 and pasted it to reused it in line 75. If you check the rules for the sheet they have started to disaggregate so we now have many rules.

Yes, this is the problem. You are inserting a new group of formatting into an existing range, breaking apart the original into a non-contiguous group of 2 and adding a new cond format. If you insert a new row 75 first, it will expand the range of the cond formatting. You can then copy 72 and paste the VALUES into row 75 (keeping the existing format and not creating a new area).

After running the code, if you continue to add new formatting areas, and split the existing ones, It makes sense to me that the code would have to be redone to reapply it and make the area contiguous again.

Steve

Reply | Quote

A different option for resetting the conditional formatting would be to manually select all but the first row (row 6) and then Conditional formatting clear rules. Then you could edit the “Applies To” range to be defined as you wish them to be. A macro to do this could be something like:

Code:

Option Explicit
Sub ReSetCondFormatting()
  Dim x As Integer
  Range("A7:R5000").FormatConditions.Delete
  For x = 1 To ActiveSheet.Cells.FormatConditions.Count
    With ActiveSheet.Cells.FormatConditions(x)
      If .AppliesTo.Cells.Address = "$F$6" Then
        .ModifyAppliesToRange Range("$F$6:$F$1000")
      ElseIf .AppliesTo.Cells.Address = "$A$6:$R$6" Then
        .ModifyAppliesToRange Range("$A$6:$R$1000")
      End If
    End With
  Next
End Sub

This only resets the ranges, but does not require creating formatting options, so is much more generic and more easily adaptable to variety of circumstances.

Steve

Reply | Quote

Hi Steve,

Yes, I understand what it is doing. And although I know that is how it behaves it isn’t the way most people (in mt experience) want Excel to behave. Hence the macro. The behaviour it isn’t consistent with normal range behaviour – if you introduce something in the middle of a range it is part of it and adopts other characteristics – OK a rule isn’t a range but one could reasonably expect this to work similarly.

Leaving my gripe about the behaviour od Excel I come back to the VBA. The problem I have is that there are several conditional formats, probaly 10 or 12, the sheet eventually grows to 1000 lines. We are carefule about inserting rows and the VBA sorts that out. That isn’t the problem, this is:

[*]to be sure if i manually clear all conditional formats so I know it’s clean
[*]apply the vba to add conditional formats which run to line 1000 or so
[*]go to the next blank line and complete some details
[*]RESULT: conditional formatting not applied even though it shows in the rules manager

Note I am not copying, inserting or deleing simple typing in the next available row. The formatting should apply according to the rules manager.

liz

Reply | Quote

Yes, I understand what it is doing. And although I know that is how it behaves it isn’t the way most people (in mt experience) want Excel to behave.

This is something you will have to take up with MS programmers. I think they chose an easier approach. What you desire I can see requiring very complex coding.

The behaviour it isn’t consistent with normal range behaviour – if you introduce something in the middle of a range it is part of it and adopts other characteristics – OK a rule isn’t a range but one could reasonably expect this to work similarly.

That is not true with other features of excel. The cond formatting seems to work like other formatting. If you copy a row that has a particular format into a range, Excel will keep the source formatting of the row, it will NOT remove the formatting of that row and use the formatting of the range you inserted it into. This can be demonstrated by having a row with a particular formatting and insert it into a range with a different format.

The inserted row will maintain its formatting, and convert the contiguous formatting into 2 separate ranges. I would not want to insert into this range and lose the source formatting and maintain the formatting of the rows around it as you desire. If I want to do that, I would insert (to maintain the format) then paste the values of the row, otherwise I would expect the behaviour it has: the inserted row maintains its formatting, it does not take the formatting of the range it is inserted into.

But regardless of the behaviour, the conditional formatting should work. So to your issue, Is it a macro issue or the conditional formatting? If you reset it without the macro, does it work as expected?

Steve

Reply | Quote

Hi Steve,

The formatting was originally set-up manually and worked, the macro was developed based on recording and some tweaks to optimise it a little. The rules look correct after the macro has run. The only odd think is how the range displayed in the rules dialogue shows and doesn’t show when I scroll – there’s no logic. the attached file has two screen dumps they are the same area just scrolled up and down (it feels like a memory-disply conflict but can’t be). Whether the area is populated in the rules seems to be random, but it is obviously applied correctly when the macro runs.

Mystified????

liz

Reply | Quote