CODEWITHSUNDEEP
vbaexcelconditional-formattingexcel-automation
Rishi
Rishi

Reputation: 1060

Error while checking an optional attribute of a conditional format in VBA

I have written two VBA subroutines:

1) To set conditional formatting (with operator, formula1 and formula2 as optional)

Sub setConditionalFormatting(sheetName As String, cellRange As String, CFcellColor As String, CFfontColor As String, CFtype As XlFormatConditionType, Optional CFoperator As Variant, Optional CFformula1 As Variant, Optional CFformula2 As Variant)
On Error GoTo Errhandler
    Dim sheet As Worksheet
    Dim cell As range
    Set sheet = Sheets(sheetName)
    sheet.Select
    Set cell = range(cellRange)
    cell.Select
    'user defined sub to print string in a file
    Call OutputString("Setting Conditional Formatting...") 

    With cell.FormatConditions.Add( _
        Type:=CFtype, _
        Operator:=CFoperator, _
        Formula1:=CFformula1, _
        Formula2:=CFformula2)

        .Interior.color = CFcellColor
        .Font.color = CFfontColor
    End With

    Call OutputString("Conditional Formatting successfully applied")
Exit Sub
Errhandler:
    'a sub for error handling task
    Call ErrorHandler(Err)
    Exit Sub
End Sub

2) To check the conditional formatting (CF) over a sheet and print the attributes for each CF:

Sub checkConditionalFormattingsOnSheet(sheetName As String, rng As String)
On Error GoTo Errhandler
    Dim cellRange As range
    Dim i As Integer
    Dim temp As Variant
    Sheets(sheetName).Select

    Set cellRange = range(rng)
    cellRange.Select

    If cellRange.FormatConditions.Count > 0 Then
        Call OutputString("Conditional formatting (CF) in sheet " + sheetName + ":")
        For i = 1 To cellRange.FormatConditions.Count
            Call OutputString(CStr(i) + ") Conditional Formatting-")
            Call OutputString("Interior Color: " + CStr(cellRange.FormatConditions(i).Interior.color))
            Call OutputString("Font Color: " + CStr(cellRange.FormatConditions(i).Font.color))
            Call OutputString("CF Type: " + CStr(cellRange.FormatConditions(i).Type))

            If IsMissing(cellRange.FormatConditions(i).Operator) Then
                Call OutputString("CF Operator: Not Applicable")
            Else
                Call OutputString("CF Operator: " + CStr(cellRange.FormatConditions(i).Operator))
            End If

            Call OutputString("Formula1: " + CStr(cellRange.FormatConditions(i).Formula1))

            If IsMissing(cellRange.FormatConditions(i).Formula2) Then
                Call OutputString("CF Formula2: Not Applicable")
            Else
                Call OutputString("Formula2: " + CStr(cellRange.FormatConditions(i).Formula2))
            End If
        Next i
    ElseIf cellRange.FormatConditions.Count = 0 Then
        Call OutputString("No conditional formatting found in sheet " + sheetName)
    End If

Exit Sub
Errhandler:
    Call ErrorHandler(Err)
    Exit Sub
End Sub

Now, when I want to set a conditional format, say, "Cells with value greater than 2 should have cell colored in RGB(198, 239, 206) and font as RGB(255, 255, 0)" by making the function call

'PS: I am not parameterizing Optional value- Formula2 here   
Call setConditionalFormatting( "MyWrkSheet", "C5:N13", RGB(198, 239, 206), RGB(255, 255, 0), xlCellValue, xlGreater, "=2")

I am getting an error at If IsMissing(cellRange.FormatConditions(i).Formula2) in checkConditionalFormattingsOnSheet:

Error: Application-defined or object-defined error HelpContext: 1000095, ErrorId: 1004

I have tried other options, such as 'Is Nothing', 'IsNull()' and passing parameters for the Formula2 as Nothing and Null respectively, but didn't have any luck!

Thanks for your time and patience in Advance! :)

Upvotes: 1

Views: 605

Answers (1)

OldUgly
OldUgly

Reputation: 2119

According to the MS Documentation .Formula2 is "Used only when the data validation conditional format Operator property is xlBetween or xlNotBetween."

Therefore, I suppose, you should check the .Operator property before trying to access .Formula2.

Something like ...

If cellRange.FormatConditions(i).Operator = xlBetween or celRange.FormatConditions(i).Operator = xlNotBetween Then
    If IsMissing(cellRange.FormatConditions(i).Formula2) Then
        Call OutputString("CF Formula2: Not Applicable")
    Else
        Call OutputString("Formula2: " + CStr(cellRange.FormatConditions(i).Formula2))
    End If
End If

Upvotes: 1

Related Questions