Dr. Demento
Well-known Member
- Joined
- Nov 2, 2010
- Messages
- 618
- Office Version
- 2019
- 2016
- Platform
- 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.
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
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.
Apologies for not including the entire code; it's really long and uses a ton of outside functions.
Thanks y'all.
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
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: