Home Modify Conditional Formatting 'AppliesTo' Range
Reply: 1

Modify Conditional Formatting 'AppliesTo' Range

Calico
1#
Calico Published in 2018-02-14 18:52:25Z

I have a sheet (lets call it Sheet1) which has two existing Conditional Formatting rules. Which apply to a range in column H. The data in Sheet1 is deleted and new data is input via a database connection. This deletion and retrieval of data means the RANGE that the conditional formatting applies to changes. Is there a way I can update the CF formatting RANGE (in the Applies to box) without deleting it and recreating it in code?

pseudo-code would be something like

With Sheets("Sheet1")
.Cells.FormatConditions(1).AppliesTo = "H4:H" & .Range("H" & Rows.Count).End(xlUp).Row
.Cells.FormatConditions(2).AppliesTo = "H4:H" & .Range("H" & Rows.Count).End(xlUp).Row
End With

Any help would be appreciated, Many Thanks Caleeco

Calico
2#
Calico Reply to 2018-02-14 21:33:12Z

Thanks @dwirony. The code below works, posting in case future readers may wish to employ the same method. Feel free to post your own answer and I will mark yours as the solution.

Sub FillDown()
'// Fill down formatting to apply CF to entire range
    With Sheets("Sheet1")
        .Range("H4").Copy
        .Range("H4:H" & .Range("H" & Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteFormats
    End With
End Sub
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31061 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO