Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Possible to export conditional formatting formulas to txt file for troubleshooting?

  1. #1
    Board Regular
    Join Date
    Nov 2014
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Possible to export conditional formatting formulas to txt file for troubleshooting?

    I have a huge excel database with 191 conditional formatting formulas.

    I need to document the process to pass it on.

    Is it possible to export conditional formatting formulas to txt file for troubleshooting and documentation.

    i have searched and googled and been unable to find anything. Is this a glitch in excel?

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,492
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    There's no built-in tool in Excel for this, just as there isn't one for documenting the formulas used in a workbook.

    You could use code to go through all the conditional formatting in the workbook and output the formulas/conditions/ranges etc involved.
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Nov 2014
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    Thanks for your quick response.

    I am still somewhat of a vba novice - mostly self taught, with the help of this forum.

    Can you possibly give an example of code to "output the formulas/conditions/ranges etc involved." to get me started?

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,492
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    Have a look here Listing Conditional Formatting.

    I've not tested the code there, but it looks interesting.
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Nov 2014
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    yep, found that last week and played around with it but I couldn't get it to work - and it's a little past my knowledge level, that's why I came here

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,492
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    How did you try the code?

    All you should need to do is goto the VBE (ALT+F11), goto Insert>Module..., copy and paste both sets of code (the sub ShowConditionalFormatting and the function FCTypeFromIndex) into the new module and then run it with F5.

    The only thing you might need to change would be Sheet1 here,
    Code:
     For Each rCell In Sheet1.Cells.SpecialCells(xlCellTypeAllFormatConditions).Cells
    to reflect the name of the worksheet for which you want to list the conditional formatting.
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Nov 2014
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    Well the top code only locked up the sheet and did not even add the sheet to list the cf's.

    Reading down through the comments, they said it would only list the first formula anyway, so no point in spending more time on that.

    I also tried the code provided by Olav Mjelde, but it didn't copy and paste well, and I had to correct a lot of syntax issues - not sure if I messed something up when doing that.

    He also did not explain what should go between some of the quotes.

    This macro did create a new workbook with the column headers but didn't fill anything in.

    I've inserted the code as edited by me below if you have time to look at it - my comments are in all caps

    Code:
    Option Explicit
    
    
    Sub ShowConditionalFormatting()
    
    
    'unprotect and unfilter the active sheet
    
    
        With ActiveSheet
            ActiveSheet.Unprotect Password:="icrr"
            If .FilterMode Then
                .ShowAllData
            End If
        End With
       
        
    Dim aResult() As Variant
    Dim sRange As String
    
    
    Dim cf As Variant
    Dim rCell As Range
    Dim colFormats As Collection
    Dim i As Long
    Dim wsOutput As Worksheet
    
    
    Application.ScreenUpdating = False
    
    
    Set colFormats = New Collection
    
    
    'Modify here to select the desired range
    'Useful when conditional formatting is applied to huge ranges and
    'you only want to see the conditional formats for the used range
    'or any other user specified range
    
    
    sRange = ActiveSheet.Range("A6:AA6").CurrentRegion.Address
    
    
    For Each rCell In ActiveSheet.Range(sRange).SpecialCells(xlCellTypeAllFormatConditions).Cells
    For i = 1 To rCell.FormatConditions.Count
    On Error Resume Next
    'Modified to let everything through
    colFormats.Add rCell.FormatConditions.Item(i), rCell.FormatConditions(i).AppliesTo.Address
    On Error GoTo 0
    Next i
    Next rCell
    
    
    'Headers for the output  IT DID CREATE A NEW WORKBOOK WITH THE HEADERS
    
    
    Set wsOutput = Workbooks.Add.Worksheets(1)
    wsOutput.Range("A1:E1").Value = Array("Type", "Range", "StopIfTrue", "Formual1", "Formual2")
    
    
    'Extract range and filter
    wsOutput.Range("J1:N1").Value = Array("Type", "Range", "StopIfTrue", "Formual1", "Formual2")
    wsOutput.Range("H1").Value = "StopIfTrue"
    
    
    'I'M NOT SURE WHAT SHOULD GO BETWEEN THE QUOTES BELOW?
    
    
    wsOutput.Range("H2").Value = "Not Sure"
    
    
    'Resize the Variant range and grab the headers
    sRange = Range("A1").Resize(colFormats.Count + 1, 5).Address
    aResult() = Range(sRange).Value
    
    
    'Move data from the collection into the variant.
    'Instead of writing cell by cell, the complete array is written at the same speed as writing one cell
    For i = 1 To colFormats.Count
    Debug.Print i
    Set cf = colFormats(i)
    aResult(i + 1, 1) = FCTypeFromIndex(cf.Type)
    aResult(i + 1, 2) = cf.AppliesTo.Address
    aResult(i + 1, 3) = cf.StopIfTrue
    On Error Resume Next
    
    
    'I'M NOT SURE WHAT SHOULD GO BETWEEN THE QUOTES BELOW?
    
    
    aResult(i + 1, 4) = "what goes here" & cf.Formula1
    aResult(i + 1, 5) = "and Here" & cf.Formula2
    On Error GoTo 0
    Next i
    'Write the result
    Range(sRange).Value = aResult()
    
    
    'Filter And extract
    Range(sRange).AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Range("H1:H2"), _
    CopyToRange:=Range("J1:N1"), _
    Unique:=True
    
    
    'Delete the redundant data from the variant
    ActiveSheet.Range("A:I").Delete
    
    
    wsOutput.Range("A1").CurrentRegion.EntireColumn.AutoFit
    
    
    Erase aResult
    
    
    Application.ScreenUpdating = False
    
    
        
        'reprotect the active sheet, allowing filtering and pivot tables
    
    
          ActiveSheet.Protect Password:="icrr", _
            DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFiltering:=True, AllowUsingPivotTables:=True
       
    End Sub
    
    
    Function FCTypeFromIndex(lIndex As Long) As String
       
        Select Case lIndex
            Case 12: FCTypeFromIndex = "Above Average"
            Case 10: FCTypeFromIndex = "Blanks"
            Case 1: FCTypeFromIndex = "Cell Value"
            Case 3: FCTypeFromIndex = "Color Scale"
            Case 4: FCTypeFromIndex = "DataBar"
            Case 16: FCTypeFromIndex = "Errors"
            Case 2: FCTypeFromIndex = "Expression"
            Case 6: FCTypeFromIndex = "Icon Sets"
            Case 14: FCTypeFromIndex = "No Blanks"
            Case 17: FCTypeFromIndex = "No Errors"
            Case 9: FCTypeFromIndex = "Text"
            Case 11: FCTypeFromIndex = "Time Period"
            Case 5: FCTypeFromIndex = "Top 10?"
            Case 8: FCTypeFromIndex = "Unique Values"
            Case Else: FCTypeFromIndex = "Unknown"
        End Select
           
    End Function

  8. #8
    Board Regular
    Join Date
    Nov 2014
    Posts
    71
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    Yay, I got this code to work!

    But 2 things:

    1.It's super slow - any way to speed up? Of course, it's looking through about 2000 cells and returning almost 200 formulas . . . but it takes almost 2 hours to run . . .

    2.I would like to send output to a new worksheet within the same workbook as the database - seems like that should be easy, but I can't seem to figure it out.

    Any assistance greatly appreciated!

    Code:
    Option Explicit
    
    
    Public Sub ShowConditionalFormatting2()
    
    
    
    
    'unprotect and unfilter the active sheet
    
    
        With ActiveSheet
            ActiveSheet.Unprotect Password:="icrr"
            If .FilterMode Then
                .ShowAllData
            End If
        End With
        
        'stop 10 minute idle popup
        
        Monitor OnTimeAction:=xlOnTimeStop
        
        'open new workbook and list all conditional formatting rules.
       
        Dim cf As Variant
        Dim rCell As Range
        Dim colFormats As Collection
        Dim i As Long
        Dim wsOutput As Worksheet
        Dim aOutput() As Variant
        
        Set colFormats = New Collection
        
        For Each rCell In Sheet1.Cells.SpecialCells(xlCellTypeAllFormatConditions).Cells
            For i = 1 To rCell.FormatConditions.Count
                With rCell.FormatConditions
                    On Error Resume Next
                        colFormats.Add .Item(i), CFSignature(.Item(i))
                    On Error GoTo 0
                End With
            Next i
        Next rCell
           
        ReDim aOutput(1 To colFormats.Count + 1, 1 To 5)
        
        Set wsOutput = Workbooks.Add.Worksheets(1)
        aOutput(1, 1) = "Type": aOutput(1, 2) = "Range"
        aOutput(1, 3) = "StopIfTrue": aOutput(1, 4) = "Formual1"
        aOutput(1, 5) = "Formual2"
        
        For i = 1 To colFormats.Count
            Set cf = colFormats.Item(i)
               
            aOutput(i + 1, 1) = FCTypeFromIndex(cf.Type)
            aOutput(i + 1, 2) = cf.AppliesTo.Address
            aOutput(i + 1, 3) = cf.StopIfTrue
            On Error Resume Next
                aOutput(i + 1, 4) = "'" & cf.Formula1
                aOutput(i + 1, 5) = "'" & cf.Formula2
            On Error GoTo 0
        Next i
       
        wsOutput.Range("A1").Resize(UBound(aOutput, 1), UBound(aOutput, 2)).Value = aOutput
        wsOutput.UsedRange.EntireColumn.AutoFit
       
        
        'reprotect the active sheet, allowing filtering and pivot tables
    
    
          ActiveSheet.Protect Password:="icrr", _
            DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFiltering:=True, AllowUsingPivotTables:=True
       
    End Sub
    'And in case you forgot, here’s how I got the type.
    
    
    Function FCTypeFromIndex(lIndex As Long) As String
       
        Select Case lIndex
            Case 12: FCTypeFromIndex = "Above Average"
            Case 10: FCTypeFromIndex = "Blanks"
            Case 1: FCTypeFromIndex = "Cell Value"
            Case 3: FCTypeFromIndex = "Color Scale"
            Case 4: FCTypeFromIndex = "DataBar"
            Case 16: FCTypeFromIndex = "Errors"
            Case 2: FCTypeFromIndex = "Expression"
            Case 6: FCTypeFromIndex = "Icon Sets"
            Case 14: FCTypeFromIndex = "No Blanks"
            Case 17: FCTypeFromIndex = "No Errors"
            Case 9: FCTypeFromIndex = "Text"
            Case 11: FCTypeFromIndex = "Time Period"
            Case 5: FCTypeFromIndex = "Top 10?"
            Case 8: FCTypeFromIndex = "Unique Values"
            Case Else: FCTypeFromIndex = "Unknown"
        End Select
           
    End Function
    Public Function CFSignature(ByRef cf As Variant) As String
        
        Dim aReturn(1 To 3) As String
        
        aReturn(1) = cf.AppliesTo.Address
        aReturn(2) = FCTypeFromIndex(cf.Type)
        On Error Resume Next
            aReturn(3) = cf.Formula1
            
        CFSignature = Join(aReturn, vbNullString)
        
    End Function

  9. #9
    New Member
    Join Date
    Dec 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    I have developed a tool in Excel VBA to Export / Import the Format Conditions of an Excel Worksheet in both XML and VBA.


    It can be downloaded from this link (so far only in Excel 2007+ (.xlsm) version) !

    The tool allows to export to Format Conditions data in an XML format that can be used later on for import to any Worksheet.
    At the same time of XML export, the VBA code that is able to set up these Format Conditions is also generated and can be later on imported into a Workbook Project.

  10. #10
    New Member
    Join Date
    Dec 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Possible to export conditional formatting formulas to txt file for troubleshooting?

    If it's only a matter of copying the Format Conditions from any Worksheet to any other within an Excel session, here is a lighter tool based on a subset of the same above code.
    JP

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •