Invalid Procedure Call or Argument Adding Conditional Format Condition

markc · Oct 4th 2006

Hi all -

I would like to apply different conditional formatting at different times with a click of a button

I setup a dummy and turned on the recorder and recorded this

Code

Range("A7:N7").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C7=1"
    Selection.FormatConditions(1).Interior.ColorIndex = 37
    Range("A1").Select
End Sub

I tried changing to this

Code

'/Conditional Format - OTHER EXPENSE B/L
        Set rngConditional = wsData.UsedRange
        With rngConditional
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=$E2=OTHER EXPENSE B/L"
            .FormatConditions(1).Interior.ColorIndex = 37
        End With
        
        wsData.Range("A1").Select

But it is returning error

Quote

Run-time error '5':
Invalid procedure call or argument

Here

Code

.FormatConditions.Add Type:=xlExpression, Formula1:="=$E2=OTHER EXPENSE B/L"

The range being considered is A:S of unknown rows
Thanks
-marc

Rich_z · Oct 4th 2006

Re: Conditional Formatting With Vba

Just out of interest have you tried recording it with your '=E2..." value ?

Dave Hawley · Oct 4th 2006

Re: Conditional Formatting With Vba

What is OTHER EXPENSE B/L?

Why not record the macro entering what you need, instead of what don't need ("=$C7=1")???

Rich_z · Oct 4th 2006

Re: Conditional Formatting With Vba

Quote from Dave Hawley

What is OTHER EXPENSE B/L?

Why not record the macro entering what you need, instead of what don't need ("=$C7=1")???

Yep. What he said.

markc · Oct 4th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

Thanks
Guess I should have at least used text in sample instead of numeric

No errors return now, but results are incorrect and inconsistent
Of the 10 unit P&L's being evaluated, none return with the line "Other Expense B/L" highlighted.

Furthermore, the lines that do come back highlighted are inconsitent
First 3 units are skipped altogether, no rows are highlighted
1 unit it Milk and Juice
Another unit it is Net Product Cost
Still a third units comes back with Direct Group Insurance.

There is no pattern to what row is highlighted
After the first highlighted row there are 177 rows until next highlighted row, then 142, then 160.

Revised snippet

Code

'/Conditional Format - OTHER EXPENSE B/L
        Set rngConditional = wsData.UsedRange
        With rngConditional
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=$E2=""OTHER EXPENSE B/L"""
            .FormatConditions(1).Interior.ColorIndex = 37
        End With
        
        wsData.Range("A1").Select

Full code below
Thanks
-marc

Code

Option Explicit


Sub comp_pl_ytd_FormatOtherExpenseBL()
    Dim wbBook As Workbook
    Dim wsData As Worksheet
    Dim rngConditional As Range
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
        .Calculation = xlCalculationManual
    End With
    
    Set wbBook = ThisWorkbook
    Set wsData = wbBook.Worksheets("Data")


    
    '/Conditional Format - OTHER EXPENSE B/L
        Set rngConditional = wsData.UsedRange
        With rngConditional
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=$E2=""OTHER EXPENSE B/L"""
            .FormatConditions(1).Interior.ColorIndex = 37
        End With
        
        wsData.Range("A1").Select
    
    
    'Cleanup
        Set wbBook = Nothing
        Set wsData = Nothing
        Set rngConditional = Nothing


    
        With Application
            .ScreenUpdating = True
            .DisplayAlerts = True
            .Calculation = xlCalculationAutomatic
        End With


End Sub

Display More

shg · Oct 4th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

When you hard-code the tested cell as $E2, then the cells in the first row of the used range (which might be row 1, or 7, or 42 ...) will test cell E2 regardless of the row on which it occurs, will it not?

If what you wish to test is column E of every row, then replace with

Code

.FormatConditions.Add Type:=xlExpression, Formula1:= "=$E" & rngConditional.Row & "=""OTHER EXPENSE B/L"""

Dave Hawley · Oct 5th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

The rows will change (it's the Column that's absolute) but the top left cell of the UsedRange will refer to $E2 as will ALL of Row 1. Regardless of row or column the reference column will always be "E".

shg · Oct 5th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

If the UsedRange on the sheet is all in rows 5 through 10, then the conditional formatting applied in row 5 will reference E2, not E5. Based on my own experience (which frequently lead me astray), I'm guessing that markc wanted to reference column E in the same row: E5 in row 5, E6 in row 6, ... . That is what my suggested change does.

Dave Hawley · Oct 5th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

Yes, I should have been clearer and stated "but the top left cell of the UsedRange will refer to $E2 as will ALL of Row 1" of the UsedRange.

I'm not sure what he needs here either. Your guess is as good as any IMO

markc · Oct 5th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

Thanks
That didn't work either

So I dropped the UsedRange in favor of

Code

lngRows = wsData.Range("A65536").End(xlUp).Row


    
    '/Conditional Format - OTHER EXPENSE B/L
        Set rngConditional = wsData.Range("$A$2:$R" & lngRows)
        With rngConditional
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:="=$E2=""OTHER EXPENSE B/L"""
            .FormatConditions(1).Interior.ColorIndex = 37
        End With

Display More

Which is not returning any results either
Although a visual inspection of Conditional Format Dialog tells an interesting story

A2 : =$E63637="OTHER EXPENSE B/L" (First cell in range)
R2026 : =$E125="OTHER EXPENSE B/L" (Last cell in range - in this instance)

Thanks
-marc

Dave Hawley · Oct 5th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

Marc, tell us what condition you want. It's no use to keep telling us "it didn't work" unless we know what it is you are trying to do!

Seems to me you don't understand absolute/relative Absolute and Relative References.

Also, see Conditional Formatting

Perhaps you want this really?

Formula1:="=A2=""OTHER EXPENSE B/L"""

markc · Oct 5th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

Thanks Dave

I think I have a handle on absolute ref's and conditional formatting when applied manually through conditional format dialog

I would like to highlight the entire row if the label in E2 = "OTHER EXPENSE B/L"

Thus the conditional format in A2 is =$E2="OTHER EXPENSE B/L"
Same for all cells on row 2

I then copy all formats down and the formula updates for each successive row
$E3, $E4, etc...

A small sample is attached
Thanks
-marc

Dave Hawley · Oct 5th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

oK, now I see said the blind man

You need to Select in this case else Excel will assume your reference are relative to the active cell. Use

Code

Set rngConditional = Sheet1.UsedRange
rngConditional.Select
 With Selection
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=$E1=""OTHER EXPENSE B/L"""
    .FormatConditions(1).Interior.ColorIndex = 37
End With

markc · Oct 5th 2006

Re: Invalid Procedure Call or Argument Adding Conditional Format Condition

Sweet success!!
Thanks for hanging in.
-marc

Invalid Procedure Call or Argument Adding Conditional Format Condition

Participate now!

Share