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 stackoverflow.com/questions/13125467/…. — LEBoyd
– LEBoyd, Commented Apr 23, 2018 at 17:49

Erin B · Accepted Answer · 2018-04-23 21:44:46Z

0

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

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

answered Apr 23, 2018 at 21:44

Erin B

909 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LEBoyd Over a year ago

Perfect! Thank you Erin!

LEBoyd · Accepted Answer · 2018-04-23 17:53:02Z

0

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.

answered Apr 23, 2018 at 17:53

LEBoyd

15113 bronze badges

Collectives™ on Stack Overflow

2010 Access VBA setting conditional formatting in Excel

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related