VBA Loop to fix Conditional Formatting ranges

[mikeymay]

I have a range that outputs a list that requires some cells t be merged dependent on how may rows of products are assigned to a customer

Above is a example of what needs to happen and in order to merged & word wrap the rows each customer needs for the products ordered I use the following

Sub ROCFRanges()

Dim wsRO As Worksheet

Dim fcAll As FormatConditions

Dim fcSingle As Object

Set wsRO = ActiveSheet

Set fcAll = wsRO.Cells.FormatConditions

For Each fcSingle In fcAll
   fcSingle.ModifyAppliesToRange Range(Range("RO_CFArea").Address)
Next

Set fcSingle = Nothing
Set wsRO = Nothing

End Sub

Sometimes when the code to set the range for each of the CFs, Excel crashes.

This has happened where anything since the last save was lost and also it happened with the file being re-opened and repaired with no lost of data but essentially, this is an issue in case it happens when the process is deployed to my client.

Does anyone have a suggestion on a solution/work-around to avoid using this specific code?


TIA

 

[JEC]

Maybe

Sub jec()
 Dim j As Long
 With ActiveSheet.UsedRange.FormatConditions
   For j = .Count To 1 Step -1
     .Item(j).ModifyAppliesToRange Range("RO_CFArea")
   Next
 End With
End Sub

 

[mikeymay]

I'll give it a go and let you know.

Maybe

Sub jec()
 Dim j As Long
 With ActiveSheet.UsedRange.FormatConditions
   For j = .Count To 1 Step -1
     .Item(j).ModifyAppliesToRange Range("RO_CFArea")
   Next
 End With
End Sub

[/QUOTE]

 

[mikeymay]

I'll give it a go and let you know.

So I've pushed a few test runs through and no issue so far so fingers crossed this has worked!