VBA: How to copy conditional formatting from one sheet to another without losing references?
I'm trying to debug I've searched everywhere and can't find a clear answer. I'm trying to copy conditional formatting rules from one worksheet to another in Excel using VBA, but I'm running into issues where the cell references in the rules do not update correctly. For example, I have set some conditional formatting on 'Sheet1' that highlights cells based on their values. When I attempt to copy these rules to 'Sheet2', the conditions are still referencing ranges on 'Sheet1', which is not what I want. I've tried using the following code to copy the formatting: ```vba Sub CopyConditionalFormatting() Dim sourceSheet As Worksheet Dim destSheet As Worksheet Set sourceSheet = ThisWorkbook.Sheets("Sheet1") Set destSheet = ThisWorkbook.Sheets("Sheet2") sourceSheet.Cells.Copy destSheet.Cells.PasteSpecial Paste:=xlPasteFormats Application.CutCopyMode = False End Sub ``` However, this approach doesn’t adjust the references based on the new sheet’s context. I also tried manually copying the rules using `FormatConditions`, but I couldn't get the references to update accordingly. Here's what I attempted: ```vba Sub CopyFormattingRules() Dim sourceCF As FormatCondition Dim destCell As Range For Each sourceCF In ThisWorkbook.Sheets("Sheet1").Range("A1:A10").FormatConditions Set destCell = ThisWorkbook.Sheets("Sheet2").Range(sourceCF.ModifyAppliesToRange.Address) destCell.FormatConditions.Add Type:=sourceCF.Type, Operator:=sourceCF.Operator, Formula1:=sourceCF.Formula1 ' Need to handle the reference updating here Next sourceCF End Sub ``` This gives me the behavior `Run-time behavior '1004': Application-defined or object-defined behavior` because `ModifyAppliesToRange.Address` is not valid for the destination sheet. I'm using Excel 365, and I'd like to replicate the conditional formatting rules accurately without hardcoding the cell references. Any guidance on how to achieve this correctly would be greatly appreciated! I'm working on a web app that needs to handle this. Any ideas what could be causing this? Any feedback is welcome!