Resetting Conditional Formatting using VBA

Question

I'm looking for some help in a Workbook.

I have 5 sets of cells that I have Conditional Formatting on.

I have a VBA to make some formatting changes to the text within the cell, but once it has made the changes the cells that have been changed has been removed from the Conditional Formatting.

I will not list the Formula's or Formatting as this is not being changed.

My problem is that in the Applies to section is being changed from...

Condition1: =$C$3:$J$3

Condition2: =$C$4:$J$4

Condition3: =$C$5:$J$5

Condition4: =$C$3:$J$5

Condition5: =$C$3:$J$7

Into this...

Condition1: =$F$3:$J$3

Condition2: =$F$4:$J$4

Condition3: =$F$5:$J$5

Condition4: =$F$3:$J$5

Condition5: =$F$3:$J$7

What I want to do is add to the VBA that changes the Applies to section, to change the Applies to back by changing the "F's" back to "C's", but I just cannot workout how to do this.

I do have other Conditional Formatting in other places in the Workbook, So I need to make sure that I apply the correction to the right set of Conditions.

Thank you in advance with any help that can be given

Neil

Answer 1

Hi,

I think the Object Model for FormatConditions is broken - I get strange results when I test setting the AppliesTo property with something like this:

    Dim fc As FormatCondition

    Set fc = Range("B1").FormatConditions(1)

    fc.AppliesTo = Range("$A$1:$B$1")

(it actually deleted the value in a cell!).

So I think you'll want to clear the CF and reapply it to the desired range from scratch. 

Also, be sure to have the correct cell Active (selected) when you apply CF (either in code or manually).

Cheers

Rich