+ Reply to Thread
Results 1 to 1 of 1

Referencing or naming particular conditional formating

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-05-2015
    Location
    czech
    MS-Off Ver
    2010
    Posts
    172

    Referencing or naming particular conditional formating

    Hello Dear Excel Masters

    The thing which bothers me is: How do I remove or reference particular formating?

    a)
    I can not use index to access particular item of FormatConditions collection, because the position in collection is volatile.

    b)
    I can not filter FormatConditions collection by address of .AppliesTo.Address because more than 1 condition can apply to same range.

    c) I can not use named range because it is translated in FormatConditions to range.

    Here is example:

    I create a named range like this:
    Dim colo As String
    Dim rng As Range
    Dim expr As String
    
    colo="MyRng"
    Set rng=Range("A1")
    expr="=A1=1"
    
    With Range(colo).FormatConditions.Add(xlExpression, Formula1:=expr)
            .Interior.Color = 100000
            .StopIfTrue = False
     End With

    How do I remove the condtion such that other conditions stay?

    Following code removes all conditions.

    '########################################
    '# Remove all Cformating in current sheet
    Public Sub remCformat(Optional rng As Range)
    Dim cf As FormatCondition
    Dim rn As Range
    If Not rng Is Nothing Then
        Set rn = rng
    Else
        Set rn = ActiveSheet.Cells
    End If
    On Error Resume Next
        For Each cf In rn.FormatConditions
            Debug.Print cformatType(cf.Type), cf.Formula1, "deleted"
            cf.Delete
        Next cf
    End Sub
    Edit:

    Ah I see I have to filter both by AppliesTo.Address and by expresion.

    The conditional formating i such a pain. I dont like it but it is much faster than applying formats stored in arrays.
    Last edited by jakopak; 02-22-2017 at 04:04 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 03-14-2014, 04:03 AM
  2. Replies: 4
    Last Post: 01-23-2014, 10:14 AM
  3. [SOLVED] Naming and referencing lists
    By katieshields in forum Excel General
    Replies: 2
    Last Post: 04-11-2013, 07:02 AM
  4. [SOLVED] Conditional Formating (Not Static) referencing a row Header
    By clbrown in forum Excel General
    Replies: 2
    Last Post: 07-19-2012, 10:06 AM
  5. Conditional Formating referencing a formula??
    By Coors in forum Excel General
    Replies: 3
    Last Post: 07-16-2010, 11:48 AM
  6. Macro sheet naming and referencing
    By ConorOB1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-10-2007, 06:13 AM
  7. Replies: 6
    Last Post: 08-14-2006, 05:00 PM
  8. Replies: 2
    Last Post: 03-27-2006, 12:10 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1