2010 Access VBA setting conditional formatting in Excel

Question

I found this answer for an Excel VBA, but I'm having trouble converting it to Access VBA (Conditional Formatting using Excel VBA code).

Here's my current code:

    with ws
         With .Cells(1, 1).FormatConditions.Add(Type:=xlExpression, Formula1:="=IF($J2=""Y"";TRUE;FALSE)")
            .Font.Color = -10092544
            .StopIfTrue = False
            StrSearchCriteria = "A2:D" & lRow
            .FormatConditions(1).ModifyAppliesToRange Range(StrSearchCriteria)
        End With
        With .Cells(1, 1).FormatConditions.Add(xlCellValue, xlEqual, "=""Closed (New)""")
            .Font.Color = -16776961
            .StopIfTrue = False
            StrSearchCriteria = "A2:K" & lRow
            .FormatConditions(2).ModifyAppliesToRange Range(StrSearchCriteria)
        End With
    End With

I'm getting the error "Invalid procedure call or argument".

What am I missing? Thanks

I found this answer https://stackoverflow.com/questions/13125467/adding-a-format-condition-in-excel-through-access-vba. — LEBoyd, Apr 23 '18 at 17:49

score 0 · Answer 1 · answered Apr 23 '18 at 17:53

I found this answer: Adding a format condition in Excel through Access VBA.

However, when I simply try to delete any existing conditional formats (as a test of the method with code from the linked answer) I receive the error "Object does not support this property or method." The code is:

with wks
    .Range("A:D").Selection.FormatConditions.Delete
end with

Any help on this is greatly appreciated.

score 0 · Accepted Answer · answered Apr 23 '18 at 21:44

0

You shouldn't need to reference the selection. Try:

.Range("A:D").FormatConditions.Delete

answered Apr 23 '18 at 21:44

Erin B

90
9

1

Perfect! Thank you Erin! – LEBoyd Apr 24 '18 at 14:20

2010 Access VBA setting conditional formatting in Excel

2 Answers2