Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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 F5SpecialCF 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 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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 F5SpecialCF 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 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
Kit
I know the code is out there...............just have to locate. Gord On Fri, 7 Jul 2006 12:19:02 -0700, Kit 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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 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. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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 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. |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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 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 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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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 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 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, _ |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I display all conditional formatting in Excel?
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 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 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, _ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deferring conditional formatting? | Excel Discussion (Misc queries) | |||
Conditional formatting of a data series in an Excel Chart? | Excel Discussion (Misc queries) | |||
Excel: Additional Conditional Formatting | Excel Worksheet Functions | |||
increase conditional formatting in Excel | Excel Worksheet Functions | |||
Excel Macro Question about Conditional Formatting | New Users to Excel |