Export Conditional Formatting Rules Excel 2007

  • Thread starter Thread starter Julian
  • Start date Start date
J

Julian

Is it possible to export conditional formatting rules, so I can apply them
to other spreadsheets instead of typing them all over again?
Julian
 
Is it possible to export conditional formatting rules, so I can apply them
to other spreadsheets instead of typing them all over again?
Julian
Click to expand...
'Paste Special - Formatting' doesn't work in Excel 2007, then?
 
I thought it might be a hidden menu somewhere with an export function few
people know about. By the way, why can't the formatting rules be kind of
exported?
Julian.
 
Julian said:
I thought it might be a hidden menu somewhere with an export function
few people know about. By the way, why can't the formatting rules be
kind of exported?
Julian.

Paul Hyett said:
'Paste Special - Formatting' doesn't work in Excel 2007, then?
Click to expand...

Here's some code I tossed together to document views I have in a
workbook. From some other testing I did in the same workbook something
similar could be done with CF ... beware, though, xl can turn your 15 or
16 CF rules into several thousand!

From the VBE, look up help on [ FormatCondition Object ] and go from
there.

----------- code begins (4 procs)

Option Explicit
Dim rw As Long
Dim str As String
Const HC As String = "Hidden Columns"

Sub GetCustomViews()
Dim v As CustomView
Application.ScreenUpdating = False

With Worksheets("Views")
.Cells(1, 1).Value = "Name"
.Cells(1, 2).Value = "Print Settings"
.Cells(1, 3).Value = "RowColSettings"
.Cells(1, 5).Value = HC
.Cells(1, 4).Value = "AutoFilter(s)"
' .Cells(1, 6).Value = "Range"
' .Cells(1, 7).Value = "Columns"
rw = 1
For Each v In ActiveWorkbook.CustomViews
v.Show
rw = rw + 1
.Cells(rw, 1).Value = v.Name
.Cells(rw, 2).Value = v.PrintSettings
.Cells(rw, 3).Value = v.RowColSettings
.Cells(rw, 4).Value = ListAutoFilters
.Cells(rw, 5).Value = ListHiddenColumns
Next v
.Activate
End With

Application.ScreenUpdating = True
End Sub

Sub HideColumns()
' Hide columns from Views!Hidden Columns column
' Test for valid activecell
Debug.Assert ActiveCell.EntireColumn.Cells(1) = HC

Set ws = Sheets("data")
ws.Columns.Hidden = False ' show all columns
Dim vCol As Variant
For Each vCol In Split(ActiveCell, ",")
If vCol <> "" Then
ws.Columns(CInt(vCol)).Hidden = True
End If
Next vCol
Set ws = Nothing
End Sub

Function ListAutoFilters() As String

Dim f As Filter
Dim w As Worksheet
Dim c1 As String
Dim c2 As String
Dim op As String
Dim fld As Long

Const ns As String = ""

On Error GoTo 999
Set w = ActiveSheet

fld = 1: str = ""
For Each f In w.AutoFilter.Filters
If f.On Then
c1 = f.Criteria1
If f.Operator Then
op = f.Operator
c2 = f.Criteria2
Else
op = ns
c2 = ns
End If
str = str & "} {," & fld & "," & c1 & "," & op & "," & c2
End If
fld = fld + 1
Next f
GoTo 9999

999 ' Error Handler
Debug.Print "Error " & Err.Number
Debug.Print Err.Description
Debug.Print "in " & Err.Source
Debug.Assert False

9999 ' Exit Function
Set w = Nothing
ListAutoFilters = str
End Function

Function ListHiddenColumns() As String
' List Hidden Columns in this view
On Error GoTo 999
str = ""
With ActiveSheet
For Each r In .Range("cyldata").Columns
If r.Hidden Then
str = str & "," & r.Column
End If
Next r
End With
ListHiddenColumns = str
GoTo 9999

999 ' Error Handler
Debug.Print "Error " & Err.Number
Debug.Print Err.Description
Debug.Print "in " & Err.Source
Debug.Assert False

9999 ' Exit Function
End Function
Click to expand...
 
Back
Top