0

I have looked over this forum, but I don't seem to find an answer to my question.

I have a dynamic range, based on the amount of columns and rows and want to format the cells according to different rules. However, I don't seem to be able to do so.

The range that needs to be formatted starts at K5 and is dynamic

Conditions:

  1. Value of cell > 0
  2. Value of row in column C = "BAU"

Whenever I run my code, I get an error '5' Invalid procedure call or argument. Here is my code:

Sub FormatAuswertung()

Dim last_row As Long, last_column As Long
Dim rngBlue As Range
Dim conditionBlue1 As FormatCondition

last_row = Worksheets("Auswertung2").Cells(rows.Count, 2).End(xlUp).Row
last_column = Worksheets("Auswertung2").Cells(4, Columns.Count).End(xlToLeft).Column

Set rngBlue = Range("K5", Cells(last_row, last_column))
Set conditionBlue1 = rngBlue.FormatConditions.Add(xlExpression, xlFormula, "=AND($C5=""BAU"",K5>0)")

With conditionBlue1
    .Interior.Color = RGB(0, 255, 255)
End With

End Sub

Can anyone tell me what I am doing wrong?

Thank you! :)

Samboff
  • 33
  • 5
  • Side note: you're missing the `Column` on the end of the `last_column = ...` line. Main note: what language is your Excel installation in? And which line throws the error? – BigBen Jun 28 '21 at 16:57
  • Thanks for the comment, yeah made a mistake when copying my code! My excel is german but to programm in VBA I need to type in English (and use the english notation). ```Set conditionBlue1 = ...``` line throws the error – Samboff Jun 28 '21 at 17:00
  • 2
    Well this works perfectly for me (English-install). That suggests that `"=AND($C5=""BAU"",K5>0)"` needs changed. What does the macro recorder return if you record adding the conditional format manually? – BigBen Jun 28 '21 at 17:00
  • 2
    Yes I changed it to the german version ```"=UND($C5=""BAU"";K5>0)"``` it works. This is very strange from Excel. Because when I want to insert a formula into a cell with VBA, I need to write the english version an VBA translates it into german. Seems to not do it for conditional formatting... But thans a lot @BigBen! :) – Samboff Jun 28 '21 at 17:05
  • 3
    Side note: you can use `.FormulaLocal` (or `.Formula2Local` in Excel 365) to insert a formula in your local notation into a cell, without translating to the English version. – BigBen Jun 28 '21 at 17:06
  • 2
    Previously: https://stackoverflow.com/questions/13247771/excel-macro-inserting-internationally-valid-formula-during-run-time – Tim Williams Jun 28 '21 at 17:20

0 Answers0