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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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.
 
Upvote 0
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?
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top