How do I display all conditional formatting in Excel?

[Kit]

Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
it is only possible to display conditional formatting cell-by-cell. This is
tedious when checking for any errors, which are easy to acquire when pasting
from one cell to another.

[Marcelo]

hi Kit,

try this,

http://www.j-walk.com/ss/excel/usertips/tip045.htm

also you could see the formulas on the cell with Ctrl+~ or TOOLS | OPTIONS

hth
regards from Brazil
Marcelo

"Kit" escreveu:

> Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> it is only possible to display conditional formatting cell-by-cell. This is
> tedious when checking for any errors, which are easy to acquire when pasting
> from one cell to another.

[Gord Dibben]

Marcelo/Kit

John's tip will only identify cells that have CF, not display the actual CF
Formula is:

You could do the same by F5>Special>CF cells.

I saw code once for getting the CF formulas onto a new sheet but have lost the
location.

Will keep looking.


Gord Dibben MS Excel MVP

On Fri, 7 Jul 2006 08:27:02 -0700, Marcelo <Marcelo@discussions.microsoft.com>
wrote:

>hi Kit,
>
>try this,
>
>http://www.j-walk.com/ss/excel/usertips/tip045.htm
>
>also you could see the formulas on the cell with Ctrl+~ or TOOLS | OPTIONS
>
>hth
>regards from Brazil
>Marcelo
>
>"Kit" escreveu:
>
>> Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
>> it is only possible to display conditional formatting cell-by-cell. This is
>> tedious when checking for any errors, which are easy to acquire when pasting
>> from one cell to another.

[Marcelo]

Hi Gord,

thanks for the feedback, i realy appreciate it

regards from Brazil
Marcelo

"Gord Dibben" escreveu:

> Marcelo/Kit
>
> John's tip will only identify cells that have CF, not display the actual CF
> Formula is:
>
> You could do the same by F5>Special>CF cells.
>
> I saw code once for getting the CF formulas onto a new sheet but have lost the
> location.
>
> Will keep looking.
>
>
> Gord Dibben MS Excel MVP
>
> On Fri, 7 Jul 2006 08:27:02 -0700, Marcelo <Marcelo@discussions.microsoft.com>
> wrote:
>
> >hi Kit,
> >
> >try this,
> >
> >http://www.j-walk.com/ss/excel/usertips/tip045.htm
> >
> >also you could see the formulas on the cell with Ctrl+~ or TOOLS | OPTIONS
> >
> >hth
> >regards from Brazil
> >Marcelo
> >
> >"Kit" escreveu:
> >
> >> Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> >> it is only possible to display conditional formatting cell-by-cell. This is
> >> tedious when checking for any errors, which are easy to acquire when pasting
> >> from one cell to another.
>
>

[Kit]

Thanks to both of you for your prompt responses! But the problem is not yet
solved. To indicate which cells have conditional formatting is one thing, to
display the actual CFs which are there - in all the cells at once - is quite
another!
I use the CF in constructing a rota. Weeks are in separate columns and
anyone not available that week is placed in 5 or six cells at the bottome of
each column. The conditional formatting turns the text red if I inadvertently
place someone on duty that week. This ought to be foolproof! It is only when
something changes the CF that it doesn't work This results in me having to
do a tedious check of all the cells to see if errors have crept in. If I
could see a full screen of CFs - and even print it - this would be
time-saving.

Regards to you both from England,

Kit

"Marcelo" wrote:

> hi Kit,
>
> try this,
>
> http://www.j-walk.com/ss/excel/usertips/tip045.htm
>
> also you could see the formulas on the cell with Ctrl+~ or TOOLS | OPTIONS
>
> hth
> regards from Brazil
> Marcelo
>
> "Kit" escreveu:
>
> > Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> > it is only possible to display conditional formatting cell-by-cell. This is
> > tedious when checking for any errors, which are easy to acquire when pasting
> > from one cell to another.

[Gord Dibben]

Kit

I know the code is out there...............just have to locate.


Gord

On Fri, 7 Jul 2006 12:19:02 -0700, Kit <Kit@discussions.microsoft.com> wrote:

>Thanks to both of you for your prompt responses! But the problem is not yet
>solved. To indicate which cells have conditional formatting is one thing, to
>display the actual CFs which are there - in all the cells at once - is quite
>another!
>I use the CF in constructing a rota. Weeks are in separate columns and
>anyone not available that week is placed in 5 or six cells at the bottome of
>each column. The conditional formatting turns the text red if I inadvertently
>place someone on duty that week. This ought to be foolproof! It is only when
>something changes the CF that it doesn't work This results in me having to
>do a tedious check of all the cells to see if errors have crept in. If I
>could see a full screen of CFs - and even print it - this would be
>time-saving.
>
>Regards to you both from England,
>
>Kit
>
>"Marcelo" wrote:
>
>> hi Kit,
>>
>> try this,
>>
>> http://www.j-walk.com/ss/excel/usertips/tip045.htm
>>
>> also you could see the formulas on the cell with Ctrl+~ or TOOLS | OPTIONS
>>
>> hth
>> regards from Brazil
>> Marcelo
>>
>> "Kit" escreveu:
>>
>> > Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
>> > it is only possible to display conditional formatting cell-by-cell. This is
>> > tedious when checking for any errors, which are easy to acquire when pasting
>> > from one cell to another.

Gord Dibben MS Excel MVP

[Kit]

Thanks Gord!
Signing off for today (20:38 BST here)

Kit

"Gord Dibben" wrote:

> Kit
>
> I know the code is out there...............just have to locate.
>
>
> Gord
>
> On Fri, 7 Jul 2006 12:19:02 -0700, Kit <Kit@discussions.microsoft.com> wrote:
>
> >Thanks to both of you for your prompt responses! But the problem is not yet
> >solved. To indicate which cells have conditional formatting is one thing, to
> >display the actual CFs which are there - in all the cells at once - is quite
> >another!
> >I use the CF in constructing a rota. Weeks are in separate columns and
> >anyone not available that week is placed in 5 or six cells at the bottome of
> >each column. The conditional formatting turns the text red if I inadvertently
> >place someone on duty that week. This ought to be foolproof! It is only when
> >something changes the CF that it doesn't work This results in me having to
> >do a tedious check of all the cells to see if errors have crept in. If I
> >could see a full screen of CFs - and even print it - this would be
> >time-saving.
> >
> >Regards to you both from England,
> >
> >Kit
> >
> >"Marcelo" wrote:
> >
> >> hi Kit,
> >>
> >> try this,
> >>
> >> http://www.j-walk.com/ss/excel/usertips/tip045.htm
> >>
> >> also you could see the formulas on the cell with Ctrl+~ or TOOLS | OPTIONS
> >>
> >> hth
> >> regards from Brazil
> >> Marcelo
> >>
> >> "Kit" escreveu:
> >>
> >> > Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> >> > it is only possible to display conditional formatting cell-by-cell. This is
> >> > tedious when checking for any errors, which are easy to acquire when pasting
> >> > from one cell to another.
>
> Gord Dibben MS Excel MVP
>

[Kevin Vaughn]

Oops. When I started my reply there was only one reply and it did not look
like it addressed the issue. Now, after I posted I see that there were
several responses and my reply was not what you were after. Unfortunately, I
started looking at the web page (I am viewing this from microsoft.com) a
couple hours ago and neglected to refresh the page (to see if there were any
new posts) before posting. Sorry about that.
--
Kevin Vaughn


"Kevin Vaughn" wrote:

> This should get you where you want. Use the Goto (I usually do ctrl-g) and
> then click Special. Then click on Conditional Format and choose All or Same.
> This will get you to the cells that have conditional formatting and then if
> you have chosen Same, you can see what the CF is by the normal means:
> Actually, after I tried this just now, I don't know if there is some reason
> it is not working for me (like perhaps maybe too many cells that have the
> same CF. I have seen it work in the past, but as of right now, the only time
> I see what the CF is is when I choose just one cell and then look at
> Format-Conditional Format. After further testing my limit SEEMS to be around
> 1600 rows (that's not exact though as my range starts on row 7. Also, I am
> looking at 3 columns. If I look at just one column ...it again fails at row
> 1600 (but works at row 1599. I do not know if this is documented anywhere or
> perhaps it is just a fluke on my workbook (I am using 2000 btw.)
>
>
> --
> Kevin Vaughn
>
>
> "Kit" wrote:
>
> > Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> > it is only possible to display conditional formatting cell-by-cell. This is
> > tedious when checking for any errors, which are easy to acquire when pasting
> > from one cell to another.

[Kevin Vaughn]

This should get you where you want. Use the Goto (I usually do ctrl-g) and
then click Special. Then click on Conditional Format and choose All or Same.
This will get you to the cells that have conditional formatting and then if
you have chosen Same, you can see what the CF is by the normal means:
Actually, after I tried this just now, I don't know if there is some reason
it is not working for me (like perhaps maybe too many cells that have the
same CF. I have seen it work in the past, but as of right now, the only time
I see what the CF is is when I choose just one cell and then look at
Format-Conditional Format. After further testing my limit SEEMS to be around
1600 rows (that's not exact though as my range starts on row 7. Also, I am
looking at 3 columns. If I look at just one column ...it again fails at row
1600 (but works at row 1599. I do not know if this is documented anywhere or
perhaps it is just a fluke on my workbook (I am using 2000 btw.)


--
Kevin Vaughn


"Kit" wrote:

> Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> it is only possible to display conditional formatting cell-by-cell. This is
> tedious when checking for any errors, which are easy to acquire when pasting
> from one cell to another.

[Kit]

Thanks for your time and well-informed efforts, Kevin!
Your phrase "I have seen it work in the past, but as of right now, the only
time I see what the CF is is when I choose just one cell and then look at
Format-Conditional Format" hits the nail on the head! The fact that you have
seen it work in the past gives me some hope!!

As I write this, I have not yet tried your instructions,but thought I would
drop you a quick word of thanks before doing so.

Kit



"Kevin Vaughn" wrote:

> Oops. When I started my reply there was only one reply and it did not look
> like it addressed the issue. Now, after I posted I see that there were
> several responses and my reply was not what you were after. Unfortunately, I
> started looking at the web page (I am viewing this from microsoft.com) a
> couple hours ago and neglected to refresh the page (to see if there were any
> new posts) before posting. Sorry about that.
> --
> Kevin Vaughn
>
>
> "Kevin Vaughn" wrote:
>
> > This should get you where you want. Use the Goto (I usually do ctrl-g) and
> > then click Special. Then click on Conditional Format and choose All or Same.
> > This will get you to the cells that have conditional formatting and then if
> > you have chosen Same, you can see what the CF is by the normal means:
> > Actually, after I tried this just now, I don't know if there is some reason
> > it is not working for me (like perhaps maybe too many cells that have the
> > same CF. I have seen it work in the past, but as of right now, the only time
> > I see what the CF is is when I choose just one cell and then look at
> > Format-Conditional Format. After further testing my limit SEEMS to be around
> > 1600 rows (that's not exact though as my range starts on row 7. Also, I am
> > looking at 3 columns. If I look at just one column ...it again fails at row
> > 1600 (but works at row 1599. I do not know if this is documented anywhere or
> > perhaps it is just a fluke on my workbook (I am using 2000 btw.)
> >
> >
> > --
> > Kevin Vaughn
> >
> >
> > "Kit" wrote:
> >
> > > Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> > > it is only possible to display conditional formatting cell-by-cell. This is
> > > tedious when checking for any errors, which are easy to acquire when pasting
> > > from one cell to another.

[Tom Hutchins]

Here's a subroutine which lists the conditional formatting conditions for
every cell on the active sheet on a new sheet at the end of the workbook.

Sub ListCondFmt()
'Declare local variables.
Dim x As Long, Rng As Range, Rx As String, Hits As Long
Dim NewWS As Worksheet, StartWS As Worksheet
Hits& = 1
Set StartWS = ActiveSheet
'Add a new worksheet to the current workbook at the end.
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NewWS = ActiveSheet
StartWS.Activate
'Find last (highest row/col) cell used on sheet.
On Error Resume Next
ActiveSheet.UsedRange
LastCell$ = FindLastCell(ActiveSheet)
'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
If LastCell$ = "ERROR" Then
LastCell$ = "A1"
End If
On Error GoTo LCFerr1
'Select all cells from A1 through the last cell.
ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & LastCell$).Select
For Each Rng In Selection
If Rng.FormatConditions.Count > 0 Then
Hits& = Hits& + 1
For x = 1 To Rng.FormatConditions.Count
If Rng.FormatConditions(x).Type = 1 Then
Select Case Rng.FormatConditions(x).Operator
Case 1:
Rx$ = "Between " &
Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
Case 2:
Rx$ = "Not between " &
Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
Case 3:
Rx$ = "Equal to " &
Rng.FormatConditions(x).Formula1
Case 4:
Rx$ = "Not equal to " &
Rng.FormatConditions(x).Formula1
Case 5:
Rx$ = "Greater than " &
Rng.FormatConditions(x).Formula1
Case 6:
Rx$ = "Less than " &
Rng.FormatConditions(x).Formula1
Case 7:
Rx$ = "Greater than or equal to " &
Rng.FormatConditions(x).Formula1
Case 8:
Rx$ = "Less than or equal to " &
Rng.FormatConditions(x).Formula1
Case Else
Rx$ = "Unknown operator " &
Rng.FormatConditions(x).Operator
End Select
ElseIf Rng.FormatConditions(x).Type = 2 Then
Rx$ = Rng.FormatConditions(x).Formula1
Else
Rx$ = "Unknown type"
End If
If x = 1 Then
NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
End If
NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
Next x
End If
Next Rng
'If no cells were found, tell user & delete the new sheet.
If Hits& = 1 Then
MsgBox "No cells with conditional formatting were found",
vbInformation, "ListCondFmt"
Application.DisplayAlerts = False
NewWS.Delete
Application.DisplayAlerts = True
GoTo Cleanup1
End If
'Add headings for the output rows.
NewWS.Cells(1, 1).Value = "Sheet"
NewWS.Cells(1, 2).Value = "Cell"
NewWS.Cells(1, 3).Value = "Condition1"
NewWS.Cells(1, 4).Value = "Condition2"
NewWS.Cells(1, 5).Value = "Condition3"
'Resize all columns on NewWS.
NewWS.Activate
Cells.Select
Cells.EntireColumn.AutoFit
Cleanup1:
'Free object variables.
Set NewWS = Nothing
Set StartWS = Nothing
'Restore the cursor.
Application.Cursor = xlDefault
Exit Sub
LCFerr1:
If Err.Number <> 0 Then
msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
End If
GoTo Cleanup1
End Sub

Function FindLastCell(Wksht As Worksheet) As String
'Returns address of last cell used (highest row & col) on specified sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With Wksht
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function

I know it's a lot of code, but just copy & paste it into a VBA module and
give it a try.

Hope this helps,

Hutch

"Kit" wrote:

> Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> it is only possible to display conditional formatting cell-by-cell. This is
> tedious when checking for any errors, which are easy to acquire when pasting
> from one cell to another.

[Tom Hutchins]

Oops - I uncharacteristically wrote your ListCondFmt code without having
specified Option Explicit. With Option Explicit on, you will need to add this
line to ListCondFmt (add it right after the other Dim statements):

Dim LastCell As String, msg1 As String

Regards;

Hutch

"Tom Hutchins" wrote:

>
> Here's a subroutine which lists the conditional formatting conditions for
> every cell on the active sheet on a new sheet at the end of the workbook.
>
> Sub ListCondFmt()
> 'Declare local variables.
> Dim x As Long, Rng As Range, Rx As String, Hits As Long
> Dim NewWS As Worksheet, StartWS As Worksheet
> Hits& = 1
> Set StartWS = ActiveSheet
> 'Add a new worksheet to the current workbook at the end.
> Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
> Set NewWS = ActiveSheet
> StartWS.Activate
> 'Find last (highest row/col) cell used on sheet.
> On Error Resume Next
> ActiveSheet.UsedRange
> LastCell$ = FindLastCell(ActiveSheet)
> 'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
> If LastCell$ = "ERROR" Then
> LastCell$ = "A1"
> End If
> On Error GoTo LCFerr1
> 'Select all cells from A1 through the last cell.
> ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & LastCell$).Select
> For Each Rng In Selection
> If Rng.FormatConditions.Count > 0 Then
> Hits& = Hits& + 1
> For x = 1 To Rng.FormatConditions.Count
> If Rng.FormatConditions(x).Type = 1 Then
> Select Case Rng.FormatConditions(x).Operator
> Case 1:
> Rx$ = "Between " &
> Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> Case 2:
> Rx$ = "Not between " &
> Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> Case 3:
> Rx$ = "Equal to " &
> Rng.FormatConditions(x).Formula1
> Case 4:
> Rx$ = "Not equal to " &
> Rng.FormatConditions(x).Formula1
> Case 5:
> Rx$ = "Greater than " &
> Rng.FormatConditions(x).Formula1
> Case 6:
> Rx$ = "Less than " &
> Rng.FormatConditions(x).Formula1
> Case 7:
> Rx$ = "Greater than or equal to " &
> Rng.FormatConditions(x).Formula1
> Case 8:
> Rx$ = "Less than or equal to " &
> Rng.FormatConditions(x).Formula1
> Case Else
> Rx$ = "Unknown operator " &
> Rng.FormatConditions(x).Operator
> End Select
> ElseIf Rng.FormatConditions(x).Type = 2 Then
> Rx$ = Rng.FormatConditions(x).Formula1
> Else
> Rx$ = "Unknown type"
> End If
> If x = 1 Then
> NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
> NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
> End If
> NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
> Next x
> End If
> Next Rng
> 'If no cells were found, tell user & delete the new sheet.
> If Hits& = 1 Then
> MsgBox "No cells with conditional formatting were found",
> vbInformation, "ListCondFmt"
> Application.DisplayAlerts = False
> NewWS.Delete
> Application.DisplayAlerts = True
> GoTo Cleanup1
> End If
> 'Add headings for the output rows.
> NewWS.Cells(1, 1).Value = "Sheet"
> NewWS.Cells(1, 2).Value = "Cell"
> NewWS.Cells(1, 3).Value = "Condition1"
> NewWS.Cells(1, 4).Value = "Condition2"
> NewWS.Cells(1, 5).Value = "Condition3"
> 'Resize all columns on NewWS.
> NewWS.Activate
> Cells.Select
> Cells.EntireColumn.AutoFit
> Cleanup1:
> 'Free object variables.
> Set NewWS = Nothing
> Set StartWS = Nothing
> 'Restore the cursor.
> Application.Cursor = xlDefault
> Exit Sub
> LCFerr1:
> If Err.Number <> 0 Then
> msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
> & Err.Source & Chr(13) & Err.Description
> MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
> End If
> GoTo Cleanup1
> End Sub
>
> Function FindLastCell(Wksht As Worksheet) As String
> 'Returns address of last cell used (highest row & col) on specified sheet
> Dim LastRow As Long
> Dim LastCol As Integer
> On Error GoTo FLCerr1
> With Wksht
> LastRow = 0
> LastCol = 0
> LastRow& = _
> .Cells.Find("*", after:=.Cells(1), _
> LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchDirection:=xlPrevious, _
> SearchOrder:=xlByRows).Row
> LastCol% = _
> .Cells.Find("*", after:=.Cells(1), _
> LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchDirection:=xlPrevious, _
> SearchOrder:=xlByColumns).Column
> End With
> FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
> Exit Function
> FLCerr1:
> FindLastCell$ = "ERROR"
> End Function
>
> I know it's a lot of code, but just copy & paste it into a VBA module and
> give it a try.
>
> Hope this helps,
>
> Hutch
>
> "Kit" wrote:
>
> > Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> > it is only possible to display conditional formatting cell-by-cell. This is
> > tedious when checking for any errors, which are easy to acquire when pasting
> > from one cell to another.

[Gord Dibben]

Tom

Works a charm.

Glad to have the code.


Gord Dibben MS Excel MVP

On Mon, 10 Jul 2006 13:30:02 -0700, Tom Hutchins
<TomHutchins@discussions.microsoft.com> wrote:

>
>Here's a subroutine which lists the conditional formatting conditions for
>every cell on the active sheet on a new sheet at the end of the workbook.
>
>Sub ListCondFmt()
>'Declare local variables.
> Dim x As Long, Rng As Range, Rx As String, Hits As Long
> Dim NewWS As Worksheet, StartWS As Worksheet
> Hits& = 1
> Set StartWS = ActiveSheet
>'Add a new worksheet to the current workbook at the end.
> Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
> Set NewWS = ActiveSheet
> StartWS.Activate
>'Find last (highest row/col) cell used on sheet.
> On Error Resume Next
> ActiveSheet.UsedRange
> LastCell$ = FindLastCell(ActiveSheet)
>'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
> If LastCell$ = "ERROR" Then
> LastCell$ = "A1"
> End If
> On Error GoTo LCFerr1
>'Select all cells from A1 through the last cell.
> ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & LastCell$).Select
> For Each Rng In Selection
> If Rng.FormatConditions.Count > 0 Then
> Hits& = Hits& + 1
> For x = 1 To Rng.FormatConditions.Count
> If Rng.FormatConditions(x).Type = 1 Then
> Select Case Rng.FormatConditions(x).Operator
> Case 1:
> Rx$ = "Between " &
>Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> Case 2:
> Rx$ = "Not between " &
>Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> Case 3:
> Rx$ = "Equal to " &
>Rng.FormatConditions(x).Formula1
> Case 4:
> Rx$ = "Not equal to " &
>Rng.FormatConditions(x).Formula1
> Case 5:
> Rx$ = "Greater than " &
>Rng.FormatConditions(x).Formula1
> Case 6:
> Rx$ = "Less than " &
>Rng.FormatConditions(x).Formula1
> Case 7:
> Rx$ = "Greater than or equal to " &
>Rng.FormatConditions(x).Formula1
> Case 8:
> Rx$ = "Less than or equal to " &
>Rng.FormatConditions(x).Formula1
> Case Else
> Rx$ = "Unknown operator " &
>Rng.FormatConditions(x).Operator
> End Select
> ElseIf Rng.FormatConditions(x).Type = 2 Then
> Rx$ = Rng.FormatConditions(x).Formula1
> Else
> Rx$ = "Unknown type"
> End If
> If x = 1 Then
> NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
> NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
> End If
> NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
> Next x
> End If
> Next Rng
>'If no cells were found, tell user & delete the new sheet.
> If Hits& = 1 Then
> MsgBox "No cells with conditional formatting were found",
>vbInformation, "ListCondFmt"
> Application.DisplayAlerts = False
> NewWS.Delete
> Application.DisplayAlerts = True
> GoTo Cleanup1
> End If
>'Add headings for the output rows.
> NewWS.Cells(1, 1).Value = "Sheet"
> NewWS.Cells(1, 2).Value = "Cell"
> NewWS.Cells(1, 3).Value = "Condition1"
> NewWS.Cells(1, 4).Value = "Condition2"
> NewWS.Cells(1, 5).Value = "Condition3"
>'Resize all columns on NewWS.
> NewWS.Activate
> Cells.Select
> Cells.EntireColumn.AutoFit
>Cleanup1:
>'Free object variables.
> Set NewWS = Nothing
> Set StartWS = Nothing
>'Restore the cursor.
> Application.Cursor = xlDefault
> Exit Sub
>LCFerr1:
> If Err.Number <> 0 Then
> msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
> & Err.Source & Chr(13) & Err.Description
> MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
> End If
> GoTo Cleanup1
>End Sub
>
>Function FindLastCell(Wksht As Worksheet) As String
>'Returns address of last cell used (highest row & col) on specified sheet
> Dim LastRow As Long
> Dim LastCol As Integer
> On Error GoTo FLCerr1
> With Wksht
> LastRow = 0
> LastCol = 0
> LastRow& = _
> .Cells.Find("*", after:=.Cells(1), _
> LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchDirection:=xlPrevious, _
> SearchOrder:=xlByRows).Row
> LastCol% = _
> .Cells.Find("*", after:=.Cells(1), _
> LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchDirection:=xlPrevious, _
> SearchOrder:=xlByColumns).Column
> End With
> FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
> Exit Function
>FLCerr1:
> FindLastCell$ = "ERROR"
>End Function
>
>I know it's a lot of code, but just copy & paste it into a VBA module and
>give it a try.
>
>Hope this helps,
>
>Hutch
>
>"Kit" wrote:
>
>> Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
>> it is only possible to display conditional formatting cell-by-cell. This is
>> tedious when checking for any errors, which are easy to acquire when pasting
>> from one cell to another.

[Kit]

As the instigator of this query, I must thank you, Tom, very much.
I have only just received notification of replies, and am mightily impressed
by the look of the code, which is beyond my comprehension (I used to be a
whizz at BASIC!!), but have not yet copied it into the appropriate place to
try, but I trust Gord Dibben's verdict,as he must have trested it - and of
course you own, who had obviously tried and tested it before posting. It will
be used for a voluntary organisation whose rota I arrange. It should result
in fool-proof operation of the EXCEL sheet for that purpose. Many thanks all
round. I intend to report back when I have tried it.

Best Wishes,

Kit

"Gord Dibben" wrote:

> Tom
>
> Works a charm.
>
> Glad to have the code.
>
>
> Gord Dibben MS Excel MVP
>
> On Mon, 10 Jul 2006 13:30:02 -0700, Tom Hutchins
> <TomHutchins@discussions.microsoft.com> wrote:
>
> >
> >Here's a subroutine which lists the conditional formatting conditions for
> >every cell on the active sheet on a new sheet at the end of the workbook.
> >
> >Sub ListCondFmt()
> >'Declare local variables.
> > Dim x As Long, Rng As Range, Rx As String, Hits As Long
> > Dim NewWS As Worksheet, StartWS As Worksheet
> > Hits& = 1
> > Set StartWS = ActiveSheet
> >'Add a new worksheet to the current workbook at the end.
> > Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
> > Set NewWS = ActiveSheet
> > StartWS.Activate
> >'Find last (highest row/col) cell used on sheet.
> > On Error Resume Next
> > ActiveSheet.UsedRange
> > LastCell$ = FindLastCell(ActiveSheet)
> >'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
> > If LastCell$ = "ERROR" Then
> > LastCell$ = "A1"
> > End If
> > On Error GoTo LCFerr1
> >'Select all cells from A1 through the last cell.
> > ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & LastCell$).Select
> > For Each Rng In Selection
> > If Rng.FormatConditions.Count > 0 Then
> > Hits& = Hits& + 1
> > For x = 1 To Rng.FormatConditions.Count
> > If Rng.FormatConditions(x).Type = 1 Then
> > Select Case Rng.FormatConditions(x).Operator
> > Case 1:
> > Rx$ = "Between " &
> >Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> > Case 2:
> > Rx$ = "Not between " &
> >Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> > Case 3:
> > Rx$ = "Equal to " &
> >Rng.FormatConditions(x).Formula1
> > Case 4:
> > Rx$ = "Not equal to " &
> >Rng.FormatConditions(x).Formula1
> > Case 5:
> > Rx$ = "Greater than " &
> >Rng.FormatConditions(x).Formula1
> > Case 6:
> > Rx$ = "Less than " &
> >Rng.FormatConditions(x).Formula1
> > Case 7:
> > Rx$ = "Greater than or equal to " &
> >Rng.FormatConditions(x).Formula1
> > Case 8:
> > Rx$ = "Less than or equal to " &
> >Rng.FormatConditions(x).Formula1
> > Case Else
> > Rx$ = "Unknown operator " &
> >Rng.FormatConditions(x).Operator
> > End Select
> > ElseIf Rng.FormatConditions(x).Type = 2 Then
> > Rx$ = Rng.FormatConditions(x).Formula1
> > Else
> > Rx$ = "Unknown type"
> > End If
> > If x = 1 Then
> > NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
> > NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
> > End If
> > NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
> > Next x
> > End If
> > Next Rng
> >'If no cells were found, tell user & delete the new sheet.
> > If Hits& = 1 Then
> > MsgBox "No cells with conditional formatting were found",
> >vbInformation, "ListCondFmt"
> > Application.DisplayAlerts = False
> > NewWS.Delete
> > Application.DisplayAlerts = True
> > GoTo Cleanup1
> > End If
> >'Add headings for the output rows.
> > NewWS.Cells(1, 1).Value = "Sheet"
> > NewWS.Cells(1, 2).Value = "Cell"
> > NewWS.Cells(1, 3).Value = "Condition1"
> > NewWS.Cells(1, 4).Value = "Condition2"
> > NewWS.Cells(1, 5).Value = "Condition3"
> >'Resize all columns on NewWS.
> > NewWS.Activate
> > Cells.Select
> > Cells.EntireColumn.AutoFit
> >Cleanup1:
> >'Free object variables.
> > Set NewWS = Nothing
> > Set StartWS = Nothing
> >'Restore the cursor.
> > Application.Cursor = xlDefault
> > Exit Sub
> >LCFerr1:
> > If Err.Number <> 0 Then
> > msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
> > & Err.Source & Chr(13) & Err.Description
> > MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
> > End If
> > GoTo Cleanup1
> >End Sub
> >
> >Function FindLastCell(Wksht As Worksheet) As String
> >'Returns address of last cell used (highest row & col) on specified sheet
> > Dim LastRow As Long
> > Dim LastCol As Integer
> > On Error GoTo FLCerr1
> > With Wksht
> > LastRow = 0
> > LastCol = 0
> > LastRow& = _
> > .Cells.Find("*", after:=.Cells(1), _
> > LookIn:=xlFormulas, LookAt:=xlWhole, _
> > SearchDirection:=xlPrevious, _
> > SearchOrder:=xlByRows).Row
> > LastCol% = _
> > .Cells.Find("*", after:=.Cells(1), _
> > LookIn:=xlFormulas, LookAt:=xlWhole, _
> > SearchDirection:=xlPrevious, _
> > SearchOrder:=xlByColumns).Column
> > End With
> > FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
> > Exit Function
> >FLCerr1:
> > FindLastCell$ = "ERROR"
> >End Function
> >
> >I know it's a lot of code, but just copy & paste it into a VBA module and
> >give it a try.
> >
> >Hope this helps,
> >
> >Hutch
> >
> >"Kit" wrote:
> >
> >> Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
> >> it is only possible to display conditional formatting cell-by-cell. This is
> >> tedious when checking for any errors, which are easy to acquire when pasting
> >> from one cell to another.
>
>

[Gord Dibben]

Kit

Just a note of caution here.

The line wraps in Tom's posting leave a bit of editing to do.

I will re-post with line-continuation characters inserted.

Those are the _ symbols

Sub ListCondFmt()
'Declare local variables.
Dim x As Long, Rng As Range, Rx As String, Hits As Long
Dim NewWS As Worksheet, StartWS As Worksheet
Hits& = 1
Set StartWS = ActiveSheet
'Add a new worksheet to the current workbook at the end.
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NewWS = ActiveSheet
StartWS.Activate
'Find last (highest row/col) cell used on sheet.
On Error Resume Next
ActiveSheet.UsedRange
lastcell$ = FindLastCell(ActiveSheet)
'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
If lastcell$ = "ERROR" Then
lastcell$ = "A1"
End If
On Error GoTo LCFerr1
'Select all cells from A1 through the last cell.
ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & lastcell$).Select
For Each Rng In Selection
If Rng.FormatConditions.Count > 0 Then
Hits& = Hits& + 1
For x = 1 To Rng.FormatConditions.Count
If Rng.FormatConditions(x).Type = 1 Then
Select Case Rng.FormatConditions(x).Operator
Case 1:
Rx$ = "Between " & _
Rng.FormatConditions(x).Formula1 & " and " & _
Rng.FormatConditions(x).Formula2
Case 2:
Rx$ = "Not between " & _
Rng.FormatConditions(x).Formula1 & " and " & _
Rng.FormatConditions(x).Formula2
Case 3:
Rx$ = "Equal to " & _
Rng.FormatConditions(x).Formula1
Case 4:
Rx$ = "Not equal to " & _
Rng.FormatConditions(x).Formula1
Case 5:
Rx$ = "Greater than " & _
Rng.FormatConditions(x).Formula1
Case 6:
Rx$ = "Less than " & _
Rng.FormatConditions(x).Formula1
Case 7:
Rx$ = "Greater than or equal to " & _
Rng.FormatConditions(x).Formula1
Case 8:
Rx$ = "Less than or equal to " & _
Rng.FormatConditions(x).Formula1
Case Else
Rx$ = "Unknown operator " & _
Rng.FormatConditions(x).Operator
End Select
ElseIf Rng.FormatConditions(x).Type = 2 Then
Rx$ = Rng.FormatConditions(x).Formula1
Else
Rx$ = "Unknown type"
End If
If x = 1 Then
NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
End If
NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
Next x
End If
Next Rng
'If no cells were found, tell user & delete the new sheet.
If Hits& = 1 Then
MsgBox "No cells with conditional formatting were found", _
vbInformation, "ListCondFmt"
Application.DisplayAlerts = False
NewWS.Delete
Application.DisplayAlerts = True
GoTo Cleanup1
End If
'Add headings for the output rows.
NewWS.Cells(1, 1).Value = "Sheet"
NewWS.Cells(1, 2).Value = "Cell"
NewWS.Cells(1, 3).Value = "Condition1"
NewWS.Cells(1, 4).Value = "Condition2"
NewWS.Cells(1, 5).Value = "Condition3"
'Resize all columns on NewWS.
NewWS.Activate
Cells.Select
Cells.EntireColumn.AutoFit
Cleanup1:
'Free object variables.
Set NewWS = Nothing
Set StartWS = Nothing
'Restore the cursor.
Application.Cursor = xlDefault
Exit Sub
LCFerr1:
If Err.Number <> 0 Then
msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
End If
GoTo Cleanup1
End Sub

Function FindLastCell(Wksht As Worksheet) As String
'Returns address of last cell used (highest row & col) on specified sheet
Dim LastRow As Long
Dim LastCol As Integer
On Error GoTo FLCerr1
With Wksht
LastRow = 0
LastCol = 0
LastRow& = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol% = _
.Cells.Find("*", after:=.Cells(1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
Exit Function
FLCerr1:
FindLastCell$ = "ERROR"
End Function



Gord



On Tue, 11 Jul 2006 09:42:01 -0700, Kit <Kit@discussions.microsoft.com> wrote:

>As the instigator of this query, I must thank you, Tom, very much.
>I have only just received notification of replies, and am mightily impressed
>by the look of the code, which is beyond my comprehension (I used to be a
>whizz at BASIC!!), but have not yet copied it into the appropriate place to
>try, but I trust Gord Dibben's verdict,as he must have trested it - and of
>course you own, who had obviously tried and tested it before posting. It will
>be used for a voluntary organisation whose rota I arrange. It should result
>in fool-proof operation of the EXCEL sheet for that purpose. Many thanks all
>round. I intend to report back when I have tried it.
>
>Best Wishes,
>
>Kit
>
>"Gord Dibben" wrote:
>
>> Tom
>>
>> Works a charm.
>>
>> Glad to have the code.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Mon, 10 Jul 2006 13:30:02 -0700, Tom Hutchins
>> <TomHutchins@discussions.microsoft.com> wrote:
>>
>> >
>> >Here's a subroutine which lists the conditional formatting conditions for
>> >every cell on the active sheet on a new sheet at the end of the workbook.
>> >
>> >Sub ListCondFmt()
>> >'Declare local variables.
>> > Dim x As Long, Rng As Range, Rx As String, Hits As Long
>> > Dim NewWS As Worksheet, StartWS As Worksheet
>> > Hits& = 1
>> > Set StartWS = ActiveSheet
>> >'Add a new worksheet to the current workbook at the end.
>> > Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
>> > Set NewWS = ActiveSheet
>> > StartWS.Activate
>> >'Find last (highest row/col) cell used on sheet.
>> > On Error Resume Next
>> > ActiveSheet.UsedRange
>> > LastCell$ = FindLastCell(ActiveSheet)
>> >'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
>> > If LastCell$ = "ERROR" Then
>> > LastCell$ = "A1"
>> > End If
>> > On Error GoTo LCFerr1
>> >'Select all cells from A1 through the last cell.
>> > ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & LastCell$).Select
>> > For Each Rng In Selection
>> > If Rng.FormatConditions.Count > 0 Then
>> > Hits& = Hits& + 1
>> > For x = 1 To Rng.FormatConditions.Count
>> > If Rng.FormatConditions(x).Type = 1 Then
>> > Select Case Rng.FormatConditions(x).Operator
>> > Case 1:
>> > Rx$ = "Between " &
>> >Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
>> > Case 2:
>> > Rx$ = "Not between " &
>> >Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
>> > Case 3:
>> > Rx$ = "Equal to " &
>> >Rng.FormatConditions(x).Formula1
>> > Case 4:
>> > Rx$ = "Not equal to " &
>> >Rng.FormatConditions(x).Formula1
>> > Case 5:
>> > Rx$ = "Greater than " &
>> >Rng.FormatConditions(x).Formula1
>> > Case 6:
>> > Rx$ = "Less than " &
>> >Rng.FormatConditions(x).Formula1
>> > Case 7:
>> > Rx$ = "Greater than or equal to " &
>> >Rng.FormatConditions(x).Formula1
>> > Case 8:
>> > Rx$ = "Less than or equal to " &
>> >Rng.FormatConditions(x).Formula1
>> > Case Else
>> > Rx$ = "Unknown operator " &
>> >Rng.FormatConditions(x).Operator
>> > End Select
>> > ElseIf Rng.FormatConditions(x).Type = 2 Then
>> > Rx$ = Rng.FormatConditions(x).Formula1
>> > Else
>> > Rx$ = "Unknown type"
>> > End If
>> > If x = 1 Then
>> > NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
>> > NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
>> > End If
>> > NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
>> > Next x
>> > End If
>> > Next Rng
>> >'If no cells were found, tell user & delete the new sheet.
>> > If Hits& = 1 Then
>> > MsgBox "No cells with conditional formatting were found",
>> >vbInformation, "ListCondFmt"
>> > Application.DisplayAlerts = False
>> > NewWS.Delete
>> > Application.DisplayAlerts = True
>> > GoTo Cleanup1
>> > End If
>> >'Add headings for the output rows.
>> > NewWS.Cells(1, 1).Value = "Sheet"
>> > NewWS.Cells(1, 2).Value = "Cell"
>> > NewWS.Cells(1, 3).Value = "Condition1"
>> > NewWS.Cells(1, 4).Value = "Condition2"
>> > NewWS.Cells(1, 5).Value = "Condition3"
>> >'Resize all columns on NewWS.
>> > NewWS.Activate
>> > Cells.Select
>> > Cells.EntireColumn.AutoFit
>> >Cleanup1:
>> >'Free object variables.
>> > Set NewWS = Nothing
>> > Set StartWS = Nothing
>> >'Restore the cursor.
>> > Application.Cursor = xlDefault
>> > Exit Sub
>> >LCFerr1:
>> > If Err.Number <> 0 Then
>> > msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
>> > & Err.Source & Chr(13) & Err.Description
>> > MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
>> > End If
>> > GoTo Cleanup1
>> >End Sub
>> >
>> >Function FindLastCell(Wksht As Worksheet) As String
>> >'Returns address of last cell used (highest row & col) on specified sheet
>> > Dim LastRow As Long
>> > Dim LastCol As Integer
>> > On Error GoTo FLCerr1
>> > With Wksht
>> > LastRow = 0
>> > LastCol = 0
>> > LastRow& = _
>> > .Cells.Find("*", after:=.Cells(1), _
>> > LookIn:=xlFormulas, LookAt:=xlWhole, _
>> > SearchDirection:=xlPrevious, _
>> > SearchOrder:=xlByRows).Row
>> > LastCol% = _
>> > .Cells.Find("*", after:=.Cells(1), _
>> > LookIn:=xlFormulas, LookAt:=xlWhole, _
>> > SearchDirection:=xlPrevious, _
>> > SearchOrder:=xlByColumns).Column
>> > End With
>> > FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
>> > Exit Function
>> >FLCerr1:
>> > FindLastCell$ = "ERROR"
>> >End Function
>> >
>> >I know it's a lot of code, but just copy & paste it into a VBA module and
>> >give it a try.
>> >
>> >Hope this helps,
>> >
>> >Hutch
>> >
>> >"Kit" wrote:
>> >
>> >> Whilst it is possible to display all the formulas on an Excel 2003 worksheet,
>> >> it is only possible to display conditional formatting cell-by-cell. This is
>> >> tedious when checking for any errors, which are easy to acquire when pasting
>> >> from one cell to another.
>>
>>

[Kit]

Renewed Thanks for that, Gord!

It happens that I have not yet tried it, so your posting is both apposite
and timely, and clearly obviates the disappointment that I might have had in
it not succeeding.
I shall try it within the next half-hour.

Very Best Wishes,

Kit Driver

"Gord Dibben" wrote:

> Kit
>
> Just a note of caution here.
>
> The line wraps in Tom's posting leave a bit of editing to do.
>
> I will re-post with line-continuation characters inserted.
>
> Those are the _ symbols
>
> Sub ListCondFmt()
> 'Declare local variables.
> Dim x As Long, Rng As Range, Rx As String, Hits As Long
> Dim NewWS As Worksheet, StartWS As Worksheet
> Hits& = 1
> Set StartWS = ActiveSheet
> 'Add a new worksheet to the current workbook at the end.
> Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
> Set NewWS = ActiveSheet
> StartWS.Activate
> 'Find last (highest row/col) cell used on sheet.
> On Error Resume Next
> ActiveSheet.UsedRange
> lastcell$ = FindLastCell(ActiveSheet)
> 'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
> If lastcell$ = "ERROR" Then
> lastcell$ = "A1"
> End If
> On Error GoTo LCFerr1
> 'Select all cells from A1 through the last cell.
> ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & lastcell$).Select
> For Each Rng In Selection
> If Rng.FormatConditions.Count > 0 Then
> Hits& = Hits& + 1
> For x = 1 To Rng.FormatConditions.Count
> If Rng.FormatConditions(x).Type = 1 Then
> Select Case Rng.FormatConditions(x).Operator
> Case 1:
> Rx$ = "Between " & _
> Rng.FormatConditions(x).Formula1 & " and " & _
> Rng.FormatConditions(x).Formula2
> Case 2:
> Rx$ = "Not between " & _
> Rng.FormatConditions(x).Formula1 & " and " & _
> Rng.FormatConditions(x).Formula2
> Case 3:
> Rx$ = "Equal to " & _
> Rng.FormatConditions(x).Formula1
> Case 4:
> Rx$ = "Not equal to " & _
> Rng.FormatConditions(x).Formula1
> Case 5:
> Rx$ = "Greater than " & _
> Rng.FormatConditions(x).Formula1
> Case 6:
> Rx$ = "Less than " & _
> Rng.FormatConditions(x).Formula1
> Case 7:
> Rx$ = "Greater than or equal to " & _
> Rng.FormatConditions(x).Formula1
> Case 8:
> Rx$ = "Less than or equal to " & _
> Rng.FormatConditions(x).Formula1
> Case Else
> Rx$ = "Unknown operator " & _
> Rng.FormatConditions(x).Operator
> End Select
> ElseIf Rng.FormatConditions(x).Type = 2 Then
> Rx$ = Rng.FormatConditions(x).Formula1
> Else
> Rx$ = "Unknown type"
> End If
> If x = 1 Then
> NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
> NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
> End If
> NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
> Next x
> End If
> Next Rng
> 'If no cells were found, tell user & delete the new sheet.
> If Hits& = 1 Then
> MsgBox "No cells with conditional formatting were found", _
> vbInformation, "ListCondFmt"
> Application.DisplayAlerts = False
> NewWS.Delete
> Application.DisplayAlerts = True
> GoTo Cleanup1
> End If
> 'Add headings for the output rows.
> NewWS.Cells(1, 1).Value = "Sheet"
> NewWS.Cells(1, 2).Value = "Cell"
> NewWS.Cells(1, 3).Value = "Condition1"
> NewWS.Cells(1, 4).Value = "Condition2"
> NewWS.Cells(1, 5).Value = "Condition3"
> 'Resize all columns on NewWS.
> NewWS.Activate
> Cells.Select
> Cells.EntireColumn.AutoFit
> Cleanup1:
> 'Free object variables.
> Set NewWS = Nothing
> Set StartWS = Nothing
> 'Restore the cursor.
> Application.Cursor = xlDefault
> Exit Sub
> LCFerr1:
> If Err.Number <> 0 Then
> msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
> & Err.Source & Chr(13) & Err.Description
> MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
> End If
> GoTo Cleanup1
> End Sub
>
> Function FindLastCell(Wksht As Worksheet) As String
> 'Returns address of last cell used (highest row & col) on specified sheet
> Dim LastRow As Long
> Dim LastCol As Integer
> On Error GoTo FLCerr1
> With Wksht
> LastRow = 0
> LastCol = 0
> LastRow& = _
> .Cells.Find("*", after:=.Cells(1), _
> LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchDirection:=xlPrevious, _
> SearchOrder:=xlByRows).Row
> LastCol% = _
> .Cells.Find("*", after:=.Cells(1), _
> LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchDirection:=xlPrevious, _
> SearchOrder:=xlByColumns).Column
> End With
> FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
> Exit Function
> FLCerr1:
> FindLastCell$ = "ERROR"
> End Function
>
>
>
> Gord
>
>
>
> On Tue, 11 Jul 2006 09:42:01 -0700, Kit <Kit@discussions.microsoft.com> wrote:
>
> >As the instigator of this query, I must thank you, Tom, very much.
> >I have only just received notification of replies, and am mightily impressed
> >by the look of the code, which is beyond my comprehension (I used to be a
> >whizz at BASIC!!), but have not yet copied it into the appropriate place to
> >try, but I trust Gord Dibben's verdict,as he must have trested it - and of
> >course you own, who had obviously tried and tested it before posting. It will
> >be used for a voluntary organisation whose rota I arrange. It should result
> >in fool-proof operation of the EXCEL sheet for that purpose. Many thanks all
> >round. I intend to report back when I have tried it.
> >
> >Best Wishes,
> >
> >Kit
> >
> >"Gord Dibben" wrote:
> >
> >> Tom
> >>
> >> Works a charm.
> >>
> >> Glad to have the code.
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Mon, 10 Jul 2006 13:30:02 -0700, Tom Hutchins
> >> <TomHutchins@discussions.microsoft.com> wrote:
> >>
> >> >
> >> >Here's a subroutine which lists the conditional formatting conditions for
> >> >every cell on the active sheet on a new sheet at the end of the workbook.
> >> >
> >> >Sub ListCondFmt()
> >> >'Declare local variables.
> >> > Dim x As Long, Rng As Range, Rx As String, Hits As Long
> >> > Dim NewWS As Worksheet, StartWS As Worksheet
> >> > Hits& = 1
> >> > Set StartWS = ActiveSheet
> >> >'Add a new worksheet to the current workbook at the end.
> >> > Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
> >> > Set NewWS = ActiveSheet
> >> > StartWS.Activate
> >> >'Find last (highest row/col) cell used on sheet.
> >> > On Error Resume Next
> >> > ActiveSheet.UsedRange
> >> > LastCell$ = FindLastCell(ActiveSheet)
> >> >'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
> >> > If LastCell$ = "ERROR" Then
> >> > LastCell$ = "A1"
> >> > End If
> >> > On Error GoTo LCFerr1
> >> >'Select all cells from A1 through the last cell.
> >> > ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & LastCell$).Select
> >> > For Each Rng In Selection
> >> > If Rng.FormatConditions.Count > 0 Then
> >> > Hits& = Hits& + 1
> >> > For x = 1 To Rng.FormatConditions.Count
> >> > If Rng.FormatConditions(x).Type = 1 Then
> >> > Select Case Rng.FormatConditions(x).Operator
> >> > Case 1:
> >> > Rx$ = "Between " &
> >> >Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> >> > Case 2:
> >> > Rx$ = "Not between " &
> >> >Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> >> > Case 3:
> >> > Rx$ = "Equal to " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 4:
> >> > Rx$ = "Not equal to " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 5:
> >> > Rx$ = "Greater than " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 6:
> >> > Rx$ = "Less than " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 7:
> >> > Rx$ = "Greater than or equal to " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 8:
> >> > Rx$ = "Less than or equal to " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case Else
> >> > Rx$ = "Unknown operator " &
> >> >Rng.FormatConditions(x).Operator
> >> > End Select
> >> > ElseIf Rng.FormatConditions(x).Type = 2 Then
> >> > Rx$ = Rng.FormatConditions(x).Formula1
> >> > Else
> >> > Rx$ = "Unknown type"
> >> > End If
> >> > If x = 1 Then
> >> > NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
> >> > NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
> >> > End If
> >> > NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
> >> > Next x
> >> > End If
> >> > Next Rng
> >> >'If no cells were found, tell user & delete the new sheet.
> >> > If Hits& = 1 Then
> >> > MsgBox "No cells with conditional formatting were found",
> >> >vbInformation, "ListCondFmt"
> >> > Application.DisplayAlerts = False
> >> > NewWS.Delete
> >> > Application.DisplayAlerts = True
> >> > GoTo Cleanup1
> >> > End If
> >> >'Add headings for the output rows.
> >> > NewWS.Cells(1, 1).Value = "Sheet"
> >> > NewWS.Cells(1, 2).Value = "Cell"
> >> > NewWS.Cells(1, 3).Value = "Condition1"
> >> > NewWS.Cells(1, 4).Value = "Condition2"
> >> > NewWS.Cells(1, 5).Value = "Condition3"
> >> >'Resize all columns on NewWS.
> >> > NewWS.Activate
> >> > Cells.Select
> >> > Cells.EntireColumn.AutoFit
> >> >Cleanup1:
> >> >'Free object variables.
> >> > Set NewWS = Nothing
> >> > Set StartWS = Nothing
> >> >'Restore the cursor.
> >> > Application.Cursor = xlDefault
> >> > Exit Sub
> >> >LCFerr1:
> >> > If Err.Number <> 0 Then
> >> > msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
> >> > & Err.Source & Chr(13) & Err.Description
> >> > MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
> >> > End If
> >> > GoTo Cleanup1
> >> >End Sub
> >> >
> >> >Function FindLastCell(Wksht As Worksheet) As String
> >> >'Returns address of last cell used (highest row & col) on specified sheet
> >> > Dim LastRow As Long
> >> > Dim LastCol As Integer
> >> > On Error GoTo FLCerr1
> >> > With Wksht
> >> > LastRow = 0
> >> > LastCol = 0
> >> > LastRow& = _
> >> > .Cells.Find("*", after:=.Cells(1), _
> >> > LookIn:=xlFormulas, LookAt:=xlWhole, _
> >> > SearchDirection:=xlPrevious, _
> >> > SearchOrder:=xlByRows).Row
> >> > LastCol% = _
> >> > .Cells.Find("*", after:=.Cells(1), _
> >> > LookIn:=xlFormulas, LookAt:=xlWhole, _
> >> > SearchDirection:=xlPrevious, _

[Kit]

Thanks Gord and Tom, I've tried the VB code and found it does what it says on
the tin!

Many, many thanks!

Kit ("silver surfer" and worker in the voluntary sector)

"Gord Dibben" wrote:

> Kit
>
> Just a note of caution here.
>
> The line wraps in Tom's posting leave a bit of editing to do.
>
> I will re-post with line-continuation characters inserted.
>
> Those are the _ symbols
>
> Sub ListCondFmt()
> 'Declare local variables.
> Dim x As Long, Rng As Range, Rx As String, Hits As Long
> Dim NewWS As Worksheet, StartWS As Worksheet
> Hits& = 1
> Set StartWS = ActiveSheet
> 'Add a new worksheet to the current workbook at the end.
> Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
> Set NewWS = ActiveSheet
> StartWS.Activate
> 'Find last (highest row/col) cell used on sheet.
> On Error Resume Next
> ActiveSheet.UsedRange
> lastcell$ = FindLastCell(ActiveSheet)
> 'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
> If lastcell$ = "ERROR" Then
> lastcell$ = "A1"
> End If
> On Error GoTo LCFerr1
> 'Select all cells from A1 through the last cell.
> ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & lastcell$).Select
> For Each Rng In Selection
> If Rng.FormatConditions.Count > 0 Then
> Hits& = Hits& + 1
> For x = 1 To Rng.FormatConditions.Count
> If Rng.FormatConditions(x).Type = 1 Then
> Select Case Rng.FormatConditions(x).Operator
> Case 1:
> Rx$ = "Between " & _
> Rng.FormatConditions(x).Formula1 & " and " & _
> Rng.FormatConditions(x).Formula2
> Case 2:
> Rx$ = "Not between " & _
> Rng.FormatConditions(x).Formula1 & " and " & _
> Rng.FormatConditions(x).Formula2
> Case 3:
> Rx$ = "Equal to " & _
> Rng.FormatConditions(x).Formula1
> Case 4:
> Rx$ = "Not equal to " & _
> Rng.FormatConditions(x).Formula1
> Case 5:
> Rx$ = "Greater than " & _
> Rng.FormatConditions(x).Formula1
> Case 6:
> Rx$ = "Less than " & _
> Rng.FormatConditions(x).Formula1
> Case 7:
> Rx$ = "Greater than or equal to " & _
> Rng.FormatConditions(x).Formula1
> Case 8:
> Rx$ = "Less than or equal to " & _
> Rng.FormatConditions(x).Formula1
> Case Else
> Rx$ = "Unknown operator " & _
> Rng.FormatConditions(x).Operator
> End Select
> ElseIf Rng.FormatConditions(x).Type = 2 Then
> Rx$ = Rng.FormatConditions(x).Formula1
> Else
> Rx$ = "Unknown type"
> End If
> If x = 1 Then
> NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
> NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
> End If
> NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
> Next x
> End If
> Next Rng
> 'If no cells were found, tell user & delete the new sheet.
> If Hits& = 1 Then
> MsgBox "No cells with conditional formatting were found", _
> vbInformation, "ListCondFmt"
> Application.DisplayAlerts = False
> NewWS.Delete
> Application.DisplayAlerts = True
> GoTo Cleanup1
> End If
> 'Add headings for the output rows.
> NewWS.Cells(1, 1).Value = "Sheet"
> NewWS.Cells(1, 2).Value = "Cell"
> NewWS.Cells(1, 3).Value = "Condition1"
> NewWS.Cells(1, 4).Value = "Condition2"
> NewWS.Cells(1, 5).Value = "Condition3"
> 'Resize all columns on NewWS.
> NewWS.Activate
> Cells.Select
> Cells.EntireColumn.AutoFit
> Cleanup1:
> 'Free object variables.
> Set NewWS = Nothing
> Set StartWS = Nothing
> 'Restore the cursor.
> Application.Cursor = xlDefault
> Exit Sub
> LCFerr1:
> If Err.Number <> 0 Then
> msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
> & Err.Source & Chr(13) & Err.Description
> MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
> End If
> GoTo Cleanup1
> End Sub
>
> Function FindLastCell(Wksht As Worksheet) As String
> 'Returns address of last cell used (highest row & col) on specified sheet
> Dim LastRow As Long
> Dim LastCol As Integer
> On Error GoTo FLCerr1
> With Wksht
> LastRow = 0
> LastCol = 0
> LastRow& = _
> .Cells.Find("*", after:=.Cells(1), _
> LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchDirection:=xlPrevious, _
> SearchOrder:=xlByRows).Row
> LastCol% = _
> .Cells.Find("*", after:=.Cells(1), _
> LookIn:=xlFormulas, LookAt:=xlWhole, _
> SearchDirection:=xlPrevious, _
> SearchOrder:=xlByColumns).Column
> End With
> FindLastCell$ = Cells(LastRow&, LastCol%).AddressLocal
> Exit Function
> FLCerr1:
> FindLastCell$ = "ERROR"
> End Function
>
>
>
> Gord
>
>
>
> On Tue, 11 Jul 2006 09:42:01 -0700, Kit <Kit@discussions.microsoft.com> wrote:
>
> >As the instigator of this query, I must thank you, Tom, very much.
> >I have only just received notification of replies, and am mightily impressed
> >by the look of the code, which is beyond my comprehension (I used to be a
> >whizz at BASIC!!), but have not yet copied it into the appropriate place to
> >try, but I trust Gord Dibben's verdict,as he must have trested it - and of
> >course you own, who had obviously tried and tested it before posting. It will
> >be used for a voluntary organisation whose rota I arrange. It should result
> >in fool-proof operation of the EXCEL sheet for that purpose. Many thanks all
> >round. I intend to report back when I have tried it.
> >
> >Best Wishes,
> >
> >Kit
> >
> >"Gord Dibben" wrote:
> >
> >> Tom
> >>
> >> Works a charm.
> >>
> >> Glad to have the code.
> >>
> >>
> >> Gord Dibben MS Excel MVP
> >>
> >> On Mon, 10 Jul 2006 13:30:02 -0700, Tom Hutchins
> >> <TomHutchins@discussions.microsoft.com> wrote:
> >>
> >> >
> >> >Here's a subroutine which lists the conditional formatting conditions for
> >> >every cell on the active sheet on a new sheet at the end of the workbook.
> >> >
> >> >Sub ListCondFmt()
> >> >'Declare local variables.
> >> > Dim x As Long, Rng As Range, Rx As String, Hits As Long
> >> > Dim NewWS As Worksheet, StartWS As Worksheet
> >> > Hits& = 1
> >> > Set StartWS = ActiveSheet
> >> >'Add a new worksheet to the current workbook at the end.
> >> > Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
> >> > Set NewWS = ActiveSheet
> >> > StartWS.Activate
> >> >'Find last (highest row/col) cell used on sheet.
> >> > On Error Resume Next
> >> > ActiveSheet.UsedRange
> >> > LastCell$ = FindLastCell(ActiveSheet)
> >> >'If FindLastCell() returned ERROR, set lastCell to A1 (empty sheet?)
> >> > If LastCell$ = "ERROR" Then
> >> > LastCell$ = "A1"
> >> > End If
> >> > On Error GoTo LCFerr1
> >> >'Select all cells from A1 through the last cell.
> >> > ActiveWorkbook.Sheets(ActiveSheet.Name).Range("A1:" & LastCell$).Select
> >> > For Each Rng In Selection
> >> > If Rng.FormatConditions.Count > 0 Then
> >> > Hits& = Hits& + 1
> >> > For x = 1 To Rng.FormatConditions.Count
> >> > If Rng.FormatConditions(x).Type = 1 Then
> >> > Select Case Rng.FormatConditions(x).Operator
> >> > Case 1:
> >> > Rx$ = "Between " &
> >> >Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> >> > Case 2:
> >> > Rx$ = "Not between " &
> >> >Rng.FormatConditions(x).Formula1 & " and " & Rng.FormatConditions(x).Formula2
> >> > Case 3:
> >> > Rx$ = "Equal to " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 4:
> >> > Rx$ = "Not equal to " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 5:
> >> > Rx$ = "Greater than " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 6:
> >> > Rx$ = "Less than " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 7:
> >> > Rx$ = "Greater than or equal to " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case 8:
> >> > Rx$ = "Less than or equal to " &
> >> >Rng.FormatConditions(x).Formula1
> >> > Case Else
> >> > Rx$ = "Unknown operator " &
> >> >Rng.FormatConditions(x).Operator
> >> > End Select
> >> > ElseIf Rng.FormatConditions(x).Type = 2 Then
> >> > Rx$ = Rng.FormatConditions(x).Formula1
> >> > Else
> >> > Rx$ = "Unknown type"
> >> > End If
> >> > If x = 1 Then
> >> > NewWS.Cells(Hits&, 1).Value = "'" & StartWS.Name
> >> > NewWS.Cells(Hits&, 2).Value = "'" & Rng.Address
> >> > End If
> >> > NewWS.Cells(Hits&, x + 2).Value = "'" & Rx$
> >> > Next x
> >> > End If
> >> > Next Rng
> >> >'If no cells were found, tell user & delete the new sheet.
> >> > If Hits& = 1 Then
> >> > MsgBox "No cells with conditional formatting were found",
> >> >vbInformation, "ListCondFmt"
> >> > Application.DisplayAlerts = False
> >> > NewWS.Delete
> >> > Application.DisplayAlerts = True
> >> > GoTo Cleanup1
> >> > End If
> >> >'Add headings for the output rows.
> >> > NewWS.Cells(1, 1).Value = "Sheet"
> >> > NewWS.Cells(1, 2).Value = "Cell"
> >> > NewWS.Cells(1, 3).Value = "Condition1"
> >> > NewWS.Cells(1, 4).Value = "Condition2"
> >> > NewWS.Cells(1, 5).Value = "Condition3"
> >> >'Resize all columns on NewWS.
> >> > NewWS.Activate
> >> > Cells.Select
> >> > Cells.EntireColumn.AutoFit
> >> >Cleanup1:
> >> >'Free object variables.
> >> > Set NewWS = Nothing
> >> > Set StartWS = Nothing
> >> >'Restore the cursor.
> >> > Application.Cursor = xlDefault
> >> > Exit Sub
> >> >LCFerr1:
> >> > If Err.Number <> 0 Then
> >> > msg1$ = "Error # " & Str(Err.Number) & " was generated by " _
> >> > & Err.Source & Chr(13) & Err.Description
> >> > MsgBox msg1$, , "ListCondFmt error", Err.HelpFile, Err.HelpContext
> >> > End If
> >> > GoTo Cleanup1
> >> >End Sub
> >> >
> >> >Function FindLastCell(Wksht As Worksheet) As String
> >> >'Returns address of last cell used (highest row & col) on specified sheet
> >> > Dim LastRow As Long
> >> > Dim LastCol As Integer
> >> > On Error GoTo FLCerr1
> >> > With Wksht
> >> > LastRow = 0
> >> > LastCol = 0
> >> > LastRow& = _
> >> > .Cells.Find("*", after:=.Cells(1), _
> >> > LookIn:=xlFormulas, LookAt:=xlWhole, _
> >> > SearchDirection:=xlPrevious, _
> >> > SearchOrder:=xlByRows).Row
> >> > LastCol% = _
> >> > .Cells.Find("*", after:=.Cells(1), _
> >> > LookIn:=xlFormulas, LookAt:=xlWhole, _
> >> > SearchDirection:=xlPrevious, _