Use of xlUniqueValues with setting conditional formatting rules in VBscript for Excel 2007

Question

I'm trying to set up conditional formatting rules for an excel sheet through a VB macro and want to use the "Duplicate Values" rule type as it is available through Excel's  GUI.
So I'm using the FormatConditions.Add method which takes up to 4 parameters (Type, Operator, Formula1, Formula2). Apparently the xlUniqueValues value must be used for Type, but I can see no way (the documentation doesn't mention how) to have it behave in "duplicate Values" mode.
I've set a conditional format to my liking in a sheet and wrote a macro to retreive the parameters: the type for a "Duplicate Values" rule is 8 (=xlUniqueValues ), but I cannot get to a parameter that switches it behaviour to Duplicate.  To my opinion the documentation is at least incomplete.

Below is my code to retreive conditional format settings on the active sheet. As you can see I've disabled trying to obtain the parameter Operator not only when Type is xlExpression (as documented), but also when it is xlUniqueValues (undocumented). It's also impossible to obtain the Formula1 and 2 parameter values when Type is xlUniqueValues . Just to be clear: I've disabled because trying to get any of these parameters results in a VB error condition.

Incidentally: I've used "$A:$Z" to addres the "full sheet" range. I can live with it, but is there a way to address the full sheet in a range object?


Sub getConditionalFormatting()
    strTitle = "Conditional formatting"
    'Count number of conditional formatting rules
    FCcount = Range("$A:$Z").FormatConditions.Count
    dum = MsgBox("There are " + Str(FCcount) + " formats set.", , strTitle)
    'Get their properties.
    If FCcount > 0 Then
        For c = 1 To FCcount
            fcType = Range("$A:$Z").FormatConditions(c).Type
            'dum = MsgBox("Format condition " + Str(c) + ": Type='" + ToStr(fcType) + "'.", , strTitle)
            If fcType = xlExpression Or fcType = xlUniqueValues Then
                fcOper = 0
            Else
                fcOper = Range("$A:$Z").FormatConditions(c).Operator
            End If
            'dum = MsgBox("Format condition " + Str(c) + ": Type='" + ToStr(fcType) + "', Operator='" + ToStr(fcOper) + "'.", , strTitle)
            If fcType = xlUniqueValues Then
                fcFrm1 = "null"
            Else
                fcFrm1 = Range("$A:$Z").FormatConditions(c).Formula1
            End If
            'dum = MsgBox("Format condition " + Str(c) + ": Type='" + ToStr(fcType) + "', Operator='" + ToStr(fcOper) + "', Formula1='" + ToStr(fcFrm1) + "'.", , strTitle)
            If fcOper = xlBetween Or fcOper = xlNotBetween Then
                fcFrm2 = Range("$A:$Z").FormatConditions(c).Formula2
            Else
                fcFrm2 = "null"
            End If
            dum = MsgBox("Format condition " + Str(c) + ": Type='" + ToStr(fcType) + "', Operator='" + ToStr(fcOper) + "', Formula1='" + ToStr(fcFrm1) + "', Formula2='" + ToStr(fcFrm2) + "'.", , strTitle)
        Next c
    End If
End Sub
'Convert variable to string safely
Function ToStr(arg) As String
    ToStr = "Error"
    On Error GoTo ErrorHandler
    If Not VarType(arg) = vbString Then
                ToStr = Str(arg)
    Else
        ToStr = arg
    End If
ErrorHandler:
End Function

Answer 1

Hi Luc1959,

This forum is for Visual Studio 2010 Beta questions and feedback. I've moved this thread to the VBA forum as you'll probably get a much quicker response there.

Cheers,
-Beth

---

Program Manager, Visual Studio Community http://msdn.com/vbasic http://msdn.com/vsto http://blogs.msdn.com/bethmassi

Answer 2

Thanks Beth!
(This was my first ever posting and I wasn't sure where to put it.)

Luc

Answer 3

Hi,

The CF for duplicate and unique does not take any formula or operator parameters. So whilst it would be more gracious of the OM to return values for Formula1, Operator etc they are not applicable. So you need to test for the Type first and then, if appropriate, the other parameters.

Rather than the full sheet use the UsedRange property.

Activesheet.usedrange.FormatConditions.Count

---

Cheers www.andypope.info

Answer 4

Thanks for your reply Andy,

Let me phrase my problems more clearly:

If I use the normal user interface of Excel to set a format condition of rule type "Format only unique or duplicate values" then choose "Duplicate" from the "Format all;" list box. Then run my macro, it tells me that the format type is 8, corresponding to xlUniqueValues (and trying to get any of the other properties results in an error). When I choose "Unique" from the same "Format all;" list box and run the macro I get exactly the same response.
I want to set up a "Duplicate" conditional formatting from within a VBA macro and I've tried adding values for parameters Operator, Formula1 and Formula2, but they appear to be ignored (documented is: "If Type is xlExpression, the Operator argument is ignored.". My conclusion is that the documentation is not up to date). I've tried to use a Type value xlDuplicateValues (not documented) in the .Add method and it throws an error (of course). Apparently there is no way to set up a conditional formatting rule for "Dduplicate" values from within a macro/using FormatConditions.Add or .Modify. Or am I overlooking something?
 
The UsedRange property only reflects the cells that contain "something". It doesn't include cells that contain nothing, but do have a conditional format rule applied to them. I have that situation in my sheets. How (other than specifying a large amount of columns) do I designate a range that includes all of the sheet?

Regards,
Luc

Answer 5

Try these 2 routines to test and set CF

Sub GetUniqueCF()

    Dim objCFUnique As UniqueValues
    Dim lngIndex As Long
    
    For lngIndex = 1 To ActiveSheet.Cells.FormatConditions.Count
        If ActiveSheet.Cells.FormatConditions(lngIndex).Type = xlUniqueValues Then
            Set objCFUnique = ActiveSheet.Cells.FormatConditions(lngIndex)
            MsgBox objCFUnique.AppliesTo.Address, , "Applies to"
            MsgBox "Duplicates = " & CStr(objCFUnique.DupeUnique = xlDuplicate)
        End If
    Next

End Sub

Sub SetUniqueCF()

    Dim objCFUnique As UniqueValues
    
    Set objCFUnique = Range("A1:A5").FormatConditions.Add(xlUniqueValues)
    With objCFUnique
        .Interior.ColorIndex = 3 ' red
        .DupeUnique = xlDuplicate
    End With
    
End Sub

Via MSDN here is  more explanation.

FormatConditions.AddUniqueValues http://msdn.microsoft.com/en-us/library/bb238839.aspx
UniqueValues Object http://msdn.microsoft.com/en-us/library/bb178544.aspx

---

Cheers www.andypope.info

Answer 6

Andy,

Thanks!
That's awesome. How was I ever going to find that it works this way!?

Luc

Here's my code now:

Sub ResetConditionalFormatting()
    Call ClearConditionalFormatting
    'Add conditional formatting 1
    Call SetFormatCondition("$A:$Z", True, xlExpression, "=OR(ROW()=1,ISBLANK(INDIRECT(ADDRESS(ROW(),1,2,TRUE))))", -1, -1, Empty)
    'Add conditional formatting 2
    Call SetFormatCondition("$A:$A", True, xlUniqueValues, "Duplicates", RGB(150, 0, 0), RGB(255, 200, 200), Empty)
    'Add conditional formatting 3
    Call SetFormatCondition("$A:$A", False, xlExpression, "=ISNUMBER(INDIRECT(ADDRESS(ROW(),1,2,TRUE)))", -1, RGB(0, 0, 0), Empty)
    'Add conditional formatting 4
    Call SetFormatCondition("$B:$B", True, xlUniqueValues, "Duplicates", RGB(150, 0, 0), RGB(255, 200, 200), Empty)
    'Add conditional formatting 5
    Call SetFormatCondition("$B:$F", False, xlBlanksCondition, True, -1, RGB(255, 255, 0), Empty)
End Sub

Sub ClearConditionalFormatting()
    'Clears any conditional formatting on the current sheet
    If ActiveSheet.Range("$A:$Z").FormatConditions.Count > 0 Then
        ActiveSheet.Range("$A:$Z").FormatConditions.Delete
    End If
End Sub

Sub SetFormatCondition(sRange As String, bStop As Boolean, iType As Integer, vFrm1, vFcolor, vBcolor, vFstyle)
    'Sets conditional formatting on the current sheet
    'Note that this routine implements only a subset of the possibilities.
    'Supported are: Type= xlExpression, xlUniqueValues, xlBlanksCondition
    'To format duplicate values, supply xlUniqueValues as Type and "Duplicates" as Formula1.
    With ActiveSheet.Range(sRange).FormatConditions.Add(iType, , vFrm1)
        With .Font
            If vFcolor >= 0 Then
                .Color = vFcolor
            End If
            If vStyle = "Normal" Then
                .Bold = False
                .Italic = False
                .Strikethrough = False
                .Subscript = False
                .Superscript = False
                .Underline = False
            ElseIf vStyle = "Bold" Then
                .Bold = True
            ElseIf vStyle = "Italic" Then
                .Italic = True
            ElseIf vStyle = "Strikethrough" Then
                .Strikethrough = True
            ElseIf vStyle = "Subscript" Then
                .Subscript = True
            ElseIf vStyle = "Superscript" Then
                .Superscript = True
            ElseIf vStyle = "Underline" Then
                .Underline = True
            End If
        End With
        If iType = xlUniqueValues Then
            If vFrm1 = "Duplicates" Then
                .DupeUnique = xlDuplicate
            End If
        End If
        If vBcolor >= 0 Then
            With .Interior
                .Color = vBcolor
            End With
        End If
        .StopIfTrue = bStop
    End With
End Sub