Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Need a Macro to turn "off and on" conditional formating

    I wonder if this is possible.

    I use an Icon set (taffic Lights) in two ranges on my spread sheet. Everything is set up the way I want it but I share my data with others and some may not want to see the Icons. Is there a way, using a macro, to turn "off and on" the Icon Conditional formatting without changing the conditions I have now already set?

    Any help is appreciated.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alrom,

    You need to program the macro to create and delect the conditional formats. At least I can't find another way to turn them on and off. Here's some sample code that works in Excel 2010.
    Note: I saved the attached file as .xls so you can run it in 2003 if necessary.
    Code:
    Option Explicit
    
    Sub MyFormat()
    
       Dim rngMyRange As Range
       
       Set rngMyRange = Range("B1:B5")  'Set your range here
    
        If rngMyRange.FormatConditions.Count = 0 Then  'Set Conditional Formats
          With rngMyRange
              .FormatConditions.Add Type:=xlExpression, Formula1:="=A1<3"
              .FormatConditions(.FormatConditions.Count).SetFirstPriority
               With rngMyRange.FormatConditions(1).Interior
                   .PatternColorIndex = xlAutomatic
                   .Color = 5287936
                   .TintAndShade = 0
               End With
              
             .FormatConditions(1).StopIfTrue = False
             
          End With
          
        Else    'Clear Conditional Formats
        
          rngMyRange.FormatConditions.Delete
          
        End If
        
    End Sub
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks for your help. While I was waiting for a reply I did a two button work around and have attached the sheet. I will review your macro and see if I can adapt it to my needs.

    Again Thanks for your help.
    Attached Files Attached Files

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Alron,

    I've attached your workbook with a new button which toggles the indicators. See the cleaned up code for ways to make things easier to understand and modify.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    alrom (2012-07-21)

  6. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Toronto, canada
    Posts
    23
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks a lot Retired Geek, I will use your code to develop the one button on/off. I continue to learn something every day. Cheers

  7. #6
    New Lounger
    Join Date
    May 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey RetiredGeek, I am trying to create something similar for work. Instead of a number, I'm trying to highlight the row that contains a name and being able to toggle on and off with a click. Any assistance would be great. Thanks.

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you elaborate. Perhaps you could attach an example of what you have and explain what you want it to do...

    Steve

  9. #8
    New Lounger
    Join Date
    May 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

  10. #9
    New Lounger
    Join Date
    May 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey Steve,
    It's for work so I can't disclose the numbers but basically what I want to do is to only use one macro button that toggles on and off COFFEE-MATE using Conditional Formatting.

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    We still need to see a representative example so we know what cells and ranges we are to deal with.

    Why a macro with conditional formatting? Why not a macro to toggle actual formatting?

    or without code at all you could use a checkbox to be the toggle for one of the conditions (the other being "COFFEE-MATE").

    Steve

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi RG

    I took your xlsb file, deleted all the code, changed it to a .xlsx file, and just added another format rule to the data range.
    I toggled this rule to a cell setting (i.e. to make an ON / OFF) setting.
    You can use a checkbox for this purpose.

    Well I think it's another way to do it, doesn't require and vba code, and the file is half the size.

    Did you have a good trip? Did you see Nessie in Scotland? I'm currently on a trip to the Caribbean.

    zeddy
    Attached Files Attached Files

Posting Permissions

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