Почему мой условный формат смещается при добавлении VBA?

Понял, лол. Установите ActiveCell, прежде чем выполнять черновую работу...

ActiveSheet.Range("A1").Activate

Excel использует автоматическую настройку диапазона, которая сбрасывает формулу при добавлении FromatCondition.

Причина, по которой условное форматирование и проверка данных демонстрируют такое странное поведение, заключается в том, что используемые ими формулы находятся за пределами обычной цепочки вычислений. Они должны быть такими, чтобы можно было ссылаться на активную ячейку в формуле. Если вы находитесь в G1, вы не можете ввести =G1="", потому что вы создадите циклическую ссылку. Но в CF или DV вы можете ввести эту формулу. Эти формулы не связаны с текущей ячейкой, в отличие от реальных формул.

Когда вы вводите формулу CF, она всегда относится к активной ячейке. Если в CF сделать формулу

=ISBLANK($G2)

и вы находитесь в формате A5, Excel преобразует его в

=ISBLANK(R[-3]C7)

и когда это помещается в CF, оно оказывается относительно ячейки, к которой оно применяется. Таким образом, в строке 2 формула выходит

=ISBLANK($G655536)

(для Excel 2003). Он смещает -3 строки и переносится в нижнюю часть электронной таблицы.

Вы можете использовать Application.ConvertFormula, чтобы сделать формулу относительно какой-либо другой ячейки. Если я нахожусь в строке 5, а начало моего диапазона находится в строке 2, я делаю формулу относительно строки 8. Таким образом, R[-3] поместит формулу в A5 как $G5 (три строки вверх от A8). ).

Sub test()

    Dim cstrDefaultProgressColumn As String
    Dim sFormula As String

    cstrDefaultProgressColumn = "$G"

    With ActiveSheet.UsedRange.Offset(1)
        .FormatConditions.Delete
        'set used row range to green interior color, if "Erledigt Datum" is not empty

        'Build formula
        sFormula = "=ISBLANK(" & cstrDefaultProgressColumn & .Row & ")"

        'convert to r1c1
        sFormula = Application.ConvertFormula(sFormula, xlA1, xlR1C1)

        'convert to a1 and make relative
        sFormula = Application.ConvertFormula(sFormula, xlR1C1, xlA1, , ActiveCell.Offset(ActiveCell.Row - .Cells(1).Row))

        With .FormatConditions.Add(Type:=xlExpression, _
                                 Formula1:=sFormula)

            .Interior.ColorIndex = 4
        End With

    End With

End Sub

Я смещаю только .Cells(1) по строкам, потому что в этом примере столбец является абсолютным. Если и строка, и столбец в вашей формуле CF являются относительными, вам нужно больше смещения. Кроме того, это работает только в том случае, если активная ячейка находится ниже первой ячейки в вашем диапазоне. Чтобы сделать его более универсальным, вам нужно будет определить, где активная ячейка находится относительно диапазона, и соответствующим образом сместиться. Если смещение поместило вас выше строки 1, вам нужно будет закодировать его так, чтобы оно относилось к ячейке ближе к нижней части общего количества строк для вашей версии Excel.

Если вы думали, что выбор был чем-то вроде кладжа, я уверен, вы согласитесь, что это еще хуже. Несмотря на то, что я ненавижу ненужный выбор и активацию, условное форматирование и проверка данных — это два места, где это неизбежное зло.

Краткий пример:

Sub Format_Range()

Dim oRange          As Range
Dim iRange_Rows     As Integer
Dim iCnt            As Integer


'First, create a named range manually in Excel (eg. "FORMAT_RANGE")
'In your case that would be range "$A$5:$H$25". 
'You only need to do this once, 
'through VBA you can afterwards dynamically adapt size + location at any time. 

'If you don't feel comfortable with that, you can create headers 
'and look for the headers dynamically in the sheet to retrieve 
'their position dynamically too. 

'Setting this range makes it independent
'from which sheet in the workbook is active
'No unnecessary .Activate is needed and certainly no hard coded "A1" cell. 
'(which makes it more potentially subject to bugs later on) 
Set oRange = ThisWorkbook.Names("FORMAT_RANGE").RefersToRange
iRange_Rows = oRange.Rows.Count

For iCnt = 1 To iRange_Rows
    If oRange(iCnt, 1) <> oRange(iCnt, 2) Then
        oRange(iCnt, 2).Interior.ColorIndex = 4
    End If
Next iCnt

End Sub

Что касается моих комментариев к другому ответу:

Если вам нужно сделать это для многих строк, то определенно быстрее загрузить весь диапазон в память (массив) и проверить условия внутри массива, после чего вы делаете запись в те ячейки, которые нужно записать (отформатированные ).
Я мог бы согласиться с тем, что этот метод не является «необходимым» в этом случае, однако это хорошая практика, потому что он гибок для многих (любых типов) настроек впоследствии и его легче отлаживать (используя непосредственные / локальные / часы window).
Я не фанат Offset, хотя и не утверждаю, что он не работает должным образом, и в некоторых ограниченных сценариях я могу сказать, что вероятность возникновения проблем «могла» быть небольшой: я испытал это на себе. некоторые бизнес-пользователи склонны использовать его постоянно (здесь смещение +3, там смещение -3, потом снова -2 и т. д.); хотя это легко писать, я могу сказать вам, что это ад пересматривать. Он также очень часто подвержен ошибкам, когда изменения вносятся конечными пользователями.
Я категорически «за» использование заголовков (хотя я также являюсь сторонником сокращения возможностей базы данных для Excel, потому что для многих это приводит к в избегании доступа), потому что это позволит вам очень гибко. Даже когда я использовал столбцы 1 и 2; лучше получить номер столбца динамически на основе расположения именованного диапазона заголовка. Если затем вставить еще один столбец, никаких ошибок не появится.

И последнее, но не менее важное: это может показаться преувеличением, но в прошлый раз я использовал модуль класса со свойствами и функциями для динамического получения всех потенциальных данных на каждом листе, проверки всех ошибок, которые я мог придумать, и некоторых дополнительных функций для выполнять определенные задачи.
Поэтому, если вам нужно много типов данных из определенного листа, вы можете создать экземпляр этого класса и получить все данные в свое распоряжение, доступные через определенные функции. Я пока не замечал, чтобы кто-то так делал, но это доставляет вам мало хлопот, несмотря на немного больше работы (вы можете использовать одни и те же принципы снова и снова).
Теперь я не думаю, что это то, что вам нужно. нужно; но может наступить день, когда вам нужно будет сделать большие инструменты для конечных пользователей, которые не знают, как это работает, но будут много жаловаться на вещи из-за того, что они могли сделать сами (даже если это не ваша «вина»); хорошо иметь это в виду.