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

BonnieM

Board Regular
Joined
Nov 3, 2014
Messages
71
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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
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.
 

BonnieM

Board Regular
Joined
Nov 3, 2014
Messages
71
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?
 

BonnieM

Board Regular
Joined
Nov 3, 2014
Messages
71
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 :confused:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
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.
 

BonnieM

Board Regular
Joined
Nov 3, 2014
Messages
71
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
 

BonnieM

Board Regular
Joined
Nov 3, 2014
Messages
71
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
 

Forum statistics

Threads
1,089,515
Messages
5,408,740
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top