{"id":857,"date":"2017-01-25T14:25:40","date_gmt":"2017-01-25T13:25:40","guid":{"rendered":"http:\/\/www.calvert.ch\/maurice\/?p=857"},"modified":"2017-01-26T10:39:52","modified_gmt":"2017-01-26T09:39:52","slug":"merge-excel-conditional-formatting","status":"publish","type":"post","link":"https:\/\/www.calvert.ch\/maurice\/2017\/01\/25\/merge-excel-conditional-formatting\/","title":{"rendered":"Merge Excel conditional formatting"},"content":{"rendered":"<p>Conditional formatting is a great tool, but after a\u00a0spreadsheet has a lot of edits, the number of conditional rules spirals out of control.<\/p>\n<p>To solve this I wrote a short VBA macro, which propagates conditional formatting (and only\u00a0conditional formatting) from the top-most cell in each column. It&#8217;s a simplistic implementation, with the following restrictions:<\/p>\n<ul>\n<li>It replicates the formatting column-by-column, copying the conditional formatting from the first (top-most) cell which has some. Make sure that the rules you want propagated are topmost in the list and include that topmost cell before running.<\/li>\n<li>Corollary: It doesn&#8217;t handle multi-column formatting.<\/li>\n<\/ul>\n<p>Paste the following code into a new module:<\/p>\n<pre>\r\nOption Explicit\r\nOption Compare Text\r\nSub Test(xls As String)\r\n    ' Open the workbook with conditional formatting.\r\n    '   Test wookbookname\r\n    ' in the immediate window\r\n    ' this will merge the conditional formatting on the first worksheet of that book\r\n    Dim wb As Workbook\r\n    Set wb = Workbooks(xls)\r\n    Dim ws As Worksheet\r\n    Set ws = wb.Worksheets(1)\r\n    MergeConditional ws\r\nEnd Sub\r\nPublic Sub MergeConditional(ws As Worksheet)\r\n    On Error GoTo FAIL\r\n    Application.EnableEvents = False\r\n    Application.ScreenUpdating = False\r\n    Dim lur As Long\r\n    lur = LastUsedRow(ws)\r\n    Dim luc As Long\r\n    luc = LastUsedCol(ws)\r\n    Dim col As Integer\r\n    Dim row As Long\r\n    For col = 1 To luc\r\n        Dim found As Boolean\r\n        found = False\r\n        Dim source As Range\r\n        For row = 1 To lur\r\n            Set source = ws.Cells(row, col)\r\n            If source.FormatConditions.Count > 0 Then\r\n                found = True\r\n                Exit For\r\n            End If\r\n        Next row\r\n        If found And row < lur Then\r\n            ws.Range(ws.Cells(row + 1, col), ws.Cells(1048576, col)).FormatConditions.Delete\r\n            Dim target As Range\r\n            Set target = ws.Range(ws.Cells(row, col), ws.Cells(lur, col))\r\n            Dim i As Integer\r\n            For i = 1 To source.FormatConditions.Count\r\n                source.FormatConditions(i).ModifyAppliesToRange target\r\n            Next i\r\n        End If ' found\r\n    Next col\r\nFAIL:\r\n    Application.EnableEvents = True\r\n    Application.ScreenUpdating = True\r\nEnd Sub\r\nPrivate Function LastUsedCol(ws As Worksheet) As Long\r\n    On Error Resume Next\r\n    LastUsedCol = 1\r\n    LastUsedCol = ws.Cells.Find(\"*\", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column\r\nEnd Function\r\nPrivate Function LastUsedRow(ws As Worksheet) As Long\r\n    On Error Resume Next\r\n    LastUsedRow = 1\r\n    LastUsedRow = ws.Cells.Find(\"*\", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).row\r\nEnd Function\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Conditional formatting is a great tool, but after a\u00a0spreadsheet has a lot of edits, the number of conditional rules spirals out of control. To solve this I wrote a short VBA macro, which propagates conditional formatting (and only\u00a0conditional formatting) from <a href='https:\/\/www.calvert.ch\/maurice\/2017\/01\/25\/merge-excel-conditional-formatting\/' class='excerpt-more'>[&#8230;]<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47],"tags":[],"class_list":["post-857","post","type-post","status-publish","format-standard","hentry","category-programming","category-47-id","post-seq-1","post-parity-odd","meta-position-corners","fix"],"_links":{"self":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/857","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/comments?post=857"}],"version-history":[{"count":11,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/857\/revisions"}],"predecessor-version":[{"id":868,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/posts\/857\/revisions\/868"}],"wp:attachment":[{"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/media?parent=857"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/categories?post=857"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.calvert.ch\/maurice\/wp-json\/wp\/v2\/tags?post=857"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}