A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

View Conditional Formats on worksheet?



 
 
Thread Tools Display Modes
  #1  
Old May 13th, 2010, 05:37 PM posted to microsoft.public.excel.misc
Bill E
external usenet poster
 
Posts: 15
Default View Conditional Formats on worksheet?

Is there an easy way to scroll over a worksheet to see what the Conditional
Formatting is for various cells? I know you can go through 'Format' -
'Conditional Formatting', but that can be very laborious. I'm reviewing a
fairly large worksheet where there are errors in the conditional formatting,
but most appear fine. I'd like to be able to scroll over cells and see what
the formatting is. Is this possible? Thanks! -Bill
  #2  
Old May 13th, 2010, 06:06 PM posted to microsoft.public.excel.misc
Bob Umlas, Excel MVP
external usenet poster
 
Posts: 275
Default View Conditional Formats on worksheet?

Certainly not by hovering the mouse over the cell, but you can set up a
worksheet_change event to bring up the formatting (and maybe set a ceertain
cell to turn this "feature" off), something like this (on the sheet-code):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Dim rg As Range
If Range("A1").Value = "No" Then Exit Sub
Err.Clear
Application.EnableEvents = False
Set rg = Application.Intersect(Target,
Cells.SpecialCells(xlCellTypeAllFormatConditions))
Application.EnableEvents = True
If Err.Number 0 Then Exit Sub
If rg Is Nothing Then Exit Sub
Application.SendKeys "%E"
Application.Dialogs(xlDialogConditionalFormatting) .Show
End Sub



"Bill E" wrote:

Is there an easy way to scroll over a worksheet to see what the Conditional
Formatting is for various cells? I know you can go through 'Format' -
'Conditional Formatting', but that can be very laborious. I'm reviewing a
fairly large worksheet where there are errors in the conditional formatting,
but most appear fine. I'd like to be able to scroll over cells and see what
the formatting is. Is this possible? Thanks! -Bill

  #3  
Old May 13th, 2010, 06:58 PM posted to microsoft.public.excel.misc
Jim Thomlinson
external usenet poster
 
Posts: 2,641
Default View Conditional Formats on worksheet?

To degug CF's I find Goto | Special to be invaluable.

On a Sheet Hit F5 | Special
The dialog that comes up will allow you to select all CF's. Do that and then
you can add italicize of such to allow you to see which cells have CF's. You
can also specify which cells have the same CF as the cell youare on which
allows you to see what is using the current CF.

With a little practice you can debug your CF's pretty quick.
--
HTH...

Jim Thomlinson


"Bill E" wrote:

Is there an easy way to scroll over a worksheet to see what the Conditional
Formatting is for various cells? I know you can go through 'Format' -
'Conditional Formatting', but that can be very laborious. I'm reviewing a
fairly large worksheet where there are errors in the conditional formatting,
but most appear fine. I'd like to be able to scroll over cells and see what
the formatting is. Is this possible? Thanks! -Bill

  #4  
Old May 13th, 2010, 08:45 PM posted to microsoft.public.excel.misc
bala_vb[_16_]
external usenet poster
 
Posts: 1
Default View Conditional Formats on worksheet?


Bob Umlas, Excel MVP;953424 Wrote:
Certainly not by hovering the mouse over the cell, but you can set up a

worksheet_change event to bring up the formatting (and maybe set a
ceertain
cell to turn this "feature" off), something like this (on the
sheet-code):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Dim rg As Range
If Range("A1").Value = "No" Then Exit Sub
Err.Clear
Application.EnableEvents = False
Set rg = Application.Intersect(Target,
Cells.SpecialCells(xlCellTypeAllFormatConditions))
Application.EnableEvents = True
If Err.Number 0 Then Exit Sub
If rg Is Nothing Then Exit Sub
Application.SendKeys "%E"
Application.Dialogs(xlDialogConditionalFormatting) .Show
End Sub



"Bill E" wrote:
-
Is there an easy way to scroll over a worksheet to see what the

Conditional
Formatting is for various cells? I know you can go through 'Format'

-
'Conditional Formatting', but that can be very laborious. I'm

reviewing a
fairly large worksheet where there are errors in the conditional

formatting,
but most appear fine. I'd like to be able to scroll over cells and

see what
the formatting is. Is this possible? Thanks! -Bill-


try this code, all the cells which have conditional formatting turns to
bold and font size to 14, you can easily debug them

Private Sub Workbook_Open()
Dim i, j As Integer
ActiveWorkbook.Sheets("sheet1").Select
For i = 1 To 100
For j = 1 To 100
If Worksheets("sheet1").Cells(i, j).FormatConditions.Count 0 Then
Worksheets("sheet1").Cells(i, j).Font.Bold = True
Worksheets("sheet1").Cells(i, j).Font.Size = 14


End If
Next j
Next i
End Sub


all the best




--
bala_vb
  #5  
Old May 27th, 2010, 10:33 PM posted to microsoft.public.excel.misc
Bill E
external usenet poster
 
Posts: 15
Default View Conditional Formats on worksheet?

Bob, Bala and Jim, thanks for your direction! Apparently I had forgotten to
check "Notify me of replies" when I posted my question. I just found your
response a few minutes ago. -Bill

"Jim Thomlinson" wrote:

To degug CF's I find Goto | Special to be invaluable.

On a Sheet Hit F5 | Special
The dialog that comes up will allow you to select all CF's. Do that and then
you can add italicize of such to allow you to see which cells have CF's. You
can also specify which cells have the same CF as the cell youare on which
allows you to see what is using the current CF.

With a little practice you can debug your CF's pretty quick.
--
HTH...

Jim Thomlinson


"Bill E" wrote:

Is there an easy way to scroll over a worksheet to see what the Conditional
Formatting is for various cells? I know you can go through 'Format' -
'Conditional Formatting', but that can be very laborious. I'm reviewing a
fairly large worksheet where there are errors in the conditional formatting,
but most appear fine. I'd like to be able to scroll over cells and see what
the formatting is. Is this possible? Thanks! -Bill

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.