Invalid Procedure Call or Argument Adding Conditional Format Condition

  • 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
    1. Range("A7:N7").Select
    2. Selection.FormatConditions.Delete
    3. Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$C7=1"
    4. Selection.FormatConditions(1).Interior.ColorIndex = 37
    5. Range("A1").Select
    6. End Sub


    I tried changing to this

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


    But it is returning error

    Quote

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


    Here

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


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

  • 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.

    Regards


    Rich

  • 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
    1. '/Conditional Format - OTHER EXPENSE B/L
    2. Set rngConditional = wsData.UsedRange
    3. With rngConditional
    4. .FormatConditions.Delete
    5. .FormatConditions.Add Type:=xlExpression, Formula1:="=$E2=""OTHER EXPENSE B/L"""
    6. .FormatConditions(1).Interior.ColorIndex = 37
    7. End With
    8. wsData.Range("A1").Select


    Full code below
    Thanks
    -marc


  • 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
    1. .FormatConditions.Add Type:=xlExpression, Formula1:= "=$E" & rngConditional.Row & "=""OTHER EXPENSE B/L"""

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • 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".

  • 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.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • 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 :)

  • Re: Invalid Procedure Call or Argument Adding Conditional Format Condition


    Thanks
    That didn't work either


    So I dropped the UsedRange in favor of


    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

  • 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"""

  • 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

  • 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
    1. Set rngConditional = Sheet1.UsedRange
    2. rngConditional.Select
    3. With Selection
    4. .FormatConditions.Delete
    5. .FormatConditions.Add Type:=xlExpression, Formula1:="=$E1=""OTHER EXPENSE B/L"""
    6. .FormatConditions(1).Interior.ColorIndex = 37
    7. End With