Differentiate CF applied to same range

Dr. Demento

Well-known Member
Joined
Nov 2, 2010
Messages
618
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I'm attempting to augment Jeff Weir's Easy way to back up CF formats sub but I'm running into an issue when more than one CF is applied to the same range. The excerpt below loops thru both the various ranges with CF (rngUsed) and each FormatCondition that is active in that range. However, not all active format conditions are pertinent to each range, even if they are activated.

Code:
  For Each sht In wbk.Worksheets  ' ~~ for all worksheets
      On Error Resume Next  ' ~~ For sheets without any CF
      Set rngUsed = sht.UsedRange.SpecialCells(xlCellTypeAllFormatConditions)
      On Error GoTo 0

      If Not rngUsed Is Nothing Then

        For Each obj In sht.Cells.FormatConditions
          i = i + 1:  str_Addr = vbNullString
 
          Select Case TypeName(obj)
            ' ---------------------
            Case "FormatCondition": Set fc = obj
              For fcCntr = 1 To fc.AppliesTo.FormatConditions.Count
                If fc.AppliesTo.FormatConditions(fcCntr).Type = obj.Type Then
                  With fc.AppliesTo.FormatConditions(fcCntr): i = i + 1
                      str_Addr = Replace(obj.AppliesTo.Address, "$", vbNullString)
                      arrCF(colDest, i) = sht.name & "_" & str_Addr
                      arrCF(colCFDV, i) = "CF_" & TypeName(obj)
                      arrCF(colType, i) = obj.Type
                      arrCF(colTime, i) = timeStamp
                      arrCF(colFormVal, i) = "'" & fc.Formula1  ' ~~ Formula of CF
                      arrCF(colFont, i) = decodeColor(.Font.color, "\")
                      arrCF(colFill, i) = decodeColor(.Interior.color, "\") ' ~~ Transforms Long into RGB String
                  End With 'rng.FormatConditions(fcCntr)
                End If
              Next fcCntr

Is there a way to ensure that only the colors that pertain to a specific CF are listed with that CF instead of all activated CF in that range?? I attempted to only evaluate those types that are the same as the current object using
Code:
If fc.AppliesTo.FormatConditions(fcCntr).Type = obj.Type Then
This translates to only evaluating xlExpressions when the object is an Expression or xlUniqueValues when the object is looking at unique values (I didn't include all possible CF object types for the sake of brevity).

The output below captures a CF formula that highlights the entire row the cursor is on. However, only the bold/blue line is pertinent to this particular CF. The other CF are pertinent to other ranges but they happen to be active in the A:AH range. How can I only report the particular color codes for a specific CF rather than include all the other CF that are active in the same range. All the other CF listed are for if specific cells have specific text.

Code:
Destination            CF \ DV Type            Subtype                Formula \ Value            Font Color             Fill Color
Daily_Updates_A:AH    CF_FormatCondition    xlExpression        =ROW(A1)=selRow            255\0\0     
Daily_Updates_A:AH    CF_FormatCondition    xlExpression        =ROW(A1)=selRow            128\128\128    
Daily_Updates_A:AH    CF_FormatCondition    xlExpression        =ROW(A1)=selRow            255\204\255
[B][COLOR=#0000ff]Daily_Updates_A:AH    CF_FormatCondition    xlExpression        =ROW(A1)=selRow            153\51\102[/COLOR][/B]    
Daily_Updates_A:AH    CF_FormatCondition    xlExpression        =ROW(A1)=selRow            0\0\255    
Daily_Updates_A:AH    CF_FormatCondition    xlExpression        =ROW(A1)=selRow            102\255\51

Apologies for not including the entire code; it's really long and uses a ton of outside functions.

Thanks y'all.
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top