Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional formatting - show (WinXP /O2003 SP1)

    Hi,

    Is it somehow possible to see which cells that have conditional formatting applied.
    eg. I have a sheet with a number of cells formatted with conditional color formatting in case a number turns negative.

    Now... I would like to see to which cells this has been applied without having to change my current cell formulas.

    Is there any switch or the like that I can turn on in order to show cells that are conditionally formatted?

    THX
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional formatting - show (WinXP /O2003 SP1)

    Not that I am aware of though it can probably be coded - how would you like to show them (bearing in mind that conditional formatting may apply borders and patterns etc)?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting - show (WinXP /O2003 SP

    Hi

    I have created a couple of macros for you which may help.

    Public Sub IsCF()
    Dim rcell As Range

    For Each rcell In Range("A1500")

    If rcell.FormatConditions.Count > 0 Then
    rcell.BorderAround ColorIndex:=3, Weight:=xlThin
    Else
    End If
    Next rcell
    End Sub







    Public Sub ClearCF()
    Dim rcell As Range
    Application.ScreenUpdating = False

    For Each rcell In Range("A1500")
    rcell.Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Next rcell
    Application.ScreenUpdating = True
    Range("A1").Select
    End Sub


    IsCF works quite quickly but I am sure someone may be able to speed up Clear CF...adjust your range to fit your needs. I hope this helps?
    Jerry

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional formatting - show (WinXP /O2003 SP

    Jerry,
    That assumes that the conditional formatting does not put borders around the cells. Your second macro also assumes that the cells did not have borders around them originally. It might be easier to write a macro to select all cells with conditional formatting rather than trying to change their formats?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditional formatting - show (WinXP /O2003 SP

    Yes good point, one I overlooked whilst using a blank workbook. Let's see what Henrik says and if I have the time I could adapt it to his purpose. Thanks for the heads up
    Jerry

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional formatting - show (WinXP /O2003 SP1)

    <P ID="edit" class=small>(Edited by rory on 21-Feb-06 14:44. Corrected error in code - thanks to Jezza for pointing it out to me!)</P>The attached may not be exactly what you want but it should get you started. It will add a comment to each cell with conditional formatting, explaining what the conditions are (I was too lazy to add all the formatting set! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>).
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting - show (WinXP /O2003 SP

    Thanks both of you.

    In this case the suggestion actually was exactly was I was looking for. <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>
    I modified it slightly, so a user can toggle the function (though Rory's heads up has been duly noted/ <img src=/S/cop.gif border=0 alt=cop width=15 height=24> )

    (My colorpalette is not MS default thus the change in Color index values)

    Public Sub IsCF(blnShow As Boolean)
    Dim rcell As Range

    If blnShow = True Then
    For Each rcell In Range("A1500")
    If rcell.FormatConditions.Count > 0 Then
    rcell.BorderAround ColorIndex:=49, Weight:=xlThin
    End If
    Next rcell
    Else
    For Each rcell In Range("A1500")
    If rcell.FormatConditions.Count > 0 Then
    rcell.BorderAround ColorIndex:=38, Weight:=xlThin
    End If
    Next rcell
    End If

    End Sub

    Toggle button:

    Private Sub cmdCFtoggle_Click()
    If cmdCFtoggle.Caption = "Show conditional formats" Then
    IsCF True
    cmdCFtoggle.Caption = "Hide conditional formats"
    Else
    cmdCFtoggle.Caption = "Hide conditional formats"
    IsCF False
    cmdCFtoggle.Caption = "Show conditional formats"
    End If
    End Sub
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional formatting - show (WinXP /O2003 SP

    Thanks rory,

    That's probably the most elegant way of doing it.

    Again - thanks for the efforts both of you.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional formatting - show (WinXP /O2003 SP

    I have just updated the code again (as attached) to amend a few things:
    1. I have added a routine to clear the comments (or strip out the additional text if the cells already were commented).
    2. I have had to add a line to activate each cell in turn when commenting as otherwise the expressions did not seem to evaluate correctly if they were relative to a cell.
    3. I have formatted the comments so they should stand out from ordinary comments.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •