Выделите активные строки, игнорируя любую ячейку с цветом заливки

У меня есть условное форматирование и VBA для выделения активных строк в электронной таблице.

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

Есть ли способ игнорировать любую ячейку, которая уже имеет цвет заливки?

Текущее условное форматирование

=CELL("row")=ROW()

Текущий VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.CutCopyMode = False Then
    Application.Calculate
End If
End Sub

Мне удалось выделить только всю активную строку.

«Я следовал инструкциям» — каким инструкциям? CF всегда имеет приоритет над существующим форматированием, поэтому я не думаю, что вы сможете заставить его игнорировать ячейки с существующей заливкой, но вы можете (например) исключить определенные столбцы из CF.

Tim Williams 29.05.2024 22:52

Обновлен исходный пост, включивший в него текущую настройку CF.

Apache MadMartigan Driver 29.05.2024 23:02

ОК, смотрите мой ответ ниже.

Tim Williams 29.05.2024 23:05
Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
1
3
73
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вам нужен способ исключить ячейки, у которых есть какая-либо существующая заливка, из правила условного форматирования «выделить строку».

Для этого вы можете добавить эту UDF в обычный модуль:

'Return True if the calling cell has no fill
'  Functions called from a CF rule formula have the
'  calling cell as `Application.Caller`
Function NoFill() As Boolean
    NoFill = (Application.Caller.Interior.ColorIndex = xlNone)
End Function

и добавьте это в формулы правил CF для выделения строк и столбцов:

=AND(ROW()=CELL("row"),NoFill())
=AND(COLUMN()=CELL("col"),NoFill())

Пример использования стрелки вниз для перемещения по строкам (только с использованием правила CF выделения строк):

Обновлено, добавлено:

В «реальном» приложении, использующем больший диапазон, это оказалось неработоспособно медленным — пересчет рабочего листа для обновления CF означает, что необходимо оценить каждую ячейку в диапазоне с этими правилами.

Итак, вот что-то более масштабируемое — оно начинается с удаления всех ранее существовавших правил выделения строк/столбцов на листе, а затем добавляется правила только для выбранной строки/столбца и только для ячеек без заливки:

В модуле рабочего листа:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Application.CutCopyMode = False Then
       AddCFRules Target.Cells(1) 'in case of >1 cell selected
    End If
End Sub

В обычном модуле:

Option Explicit

'called from Selection_change event handler
Sub AddCFRules(c As Range)
    Const NUM_ROWS As Long = 1000 'adjust to suit...
    Const NUM_COLS As Long = 500   'adjust to suit...
    With c.Worksheet
        RemoveRowColumnRules .Cells '#### changed to only delete some CF rules###
        DoEvents
        AddCFRule .Cells(c.row, 1).Resize(1, NUM_COLS)     'row highlight
        AddCFRule .Cells(1, c.Column).Resize(NUM_ROWS, 1)  'column highlight
        .Calculate
    End With
End Sub

'add a rule to hilight a range
Sub AddCFRule(rng As Range)
   Dim fc As FormatCondition, cfRange As Range
    Set cfRange = UnFilledRange(rng)    'only unfilled cells
    If Not cfRange Is Nothing Then      'any unfilled cells?
        With cfRange.FormatConditions.Add(Type:=xlExpression, Formula1: = "=TRUE")
            .StopIfTrue = False
            With .Interior
                .PatternColorIndex = xlAutomatic
                .Color = vbYellow
                .TintAndShade = 0
            End With
        End With
    End If
End Sub

'scan all cells in `rng` and return a Range with only unfilled cells
Function UnFilledRange(rng As Range) As Range
    Dim c As Range
    For Each c In rng
        With c.Interior
            If (.ColorIndex = xlNone Or .Color = vbWhite) Then
                If UnFilledRange Is Nothing Then
                    Set UnFilledRange = c 'first cell
                Else
                    Set UnFilledRange = Application.Union(UnFilledRange, c) 'all other cells
                End If
            End If
        End With
    Next c
End Function

'Delete any formula-based CF rules with "=TRUE" as the formula
'Allows other rules to be used on the sheet
Sub RemoveRowColumnRules(rng As Range)
    Dim i As Long, fc As Object
    'Debug.Print "checking CF rules:" & rng.FormatConditions.Count
    For i = rng.FormatConditions.Count To 1 Step -1
        Set fc = rng.FormatConditions(i)
        If fc.Type = xlExpression Then
            If fc.Formula1 = "=TRUE" Then fc.Delete
        End If
    Next i
End Sub

Исключать заполненные ячейки из диапазона CF немного неудобно: в идеале мы бы добавили ту же формулу, которая использовалась в первой итерации, но по какой-то причине кажется невозможным создать правило CF в VBA с использованием этой формулы. например:

With someRange.FormatConditions.Add( _
            Type:=xlExpression, _
            Formula1: = "=AND(ROW()=CELL(""row""),NoFill())")

завершается сбоем без ошибок, и выполнение кода останавливается на этом этапе.
Использование Formula1: = "=ROW()=CELL(""row"")" работает так, как ожидалось.

Позже: я обнаружил, что если вы используете глобальную переменную, например

Dim skip as Boolean

и используйте этот флаг для выхода из пользовательской функции, на которую ссылается формула CF:

Function NoFill() As Boolean
    If skip Then Exit Function
    NoFill = (Application.Caller.Interior.ColorIndex = xlNone)
End Function

тогда вы можете установить skip на True при применении правила CF через VBA, и правило будет успешно установлено без нарушений. Закончив добавление правил, установите skip на False.

Пока что я недостаточно умен в Excel, чтобы включить то, что вы сделали... хотя все еще пытаюсь

Apache MadMartigan Driver 29.05.2024 23:31

Извините, мой ответ был более сложным, чем требовалось, отредактировано для упрощения.

Tim Williams 29.05.2024 23:54

И даже больше....

Tim Williams 30.05.2024 01:12

Раньше я думал, что у меня отличная смекалка....оказалось, что это всего лишь армейская смекалка, а этого очень не хватает. Большое спасибо за помощь в этом.

Apache MadMartigan Driver 30.05.2024 15:41

Приятно слышать, что все получается. Если это ответ на ваш вопрос, отметьте его как «Принято», чтобы помочь тем, кто придет позже с подобной проблемой.

Tim Williams 30.05.2024 17:51

Возможно, я слишком рано говорил о том, что все работает. Попробую кратко описать, что происходит.

Apache MadMartigan Driver 30.05.2024 18:08

Используя уже существующие CF и VBA в качестве отправной точки отсчета, когда я перемещаюсь по листу, строки и столбцы выделяются цветом (центральная точка - это любая активная ячейка), которую я добавил в модуль, и нет никаких изменений, которые, я считаю, правильно, потому что еще нет другой ссылки (CF) на «NoFill». Однако, когда я делаю изменение CF, он рассматривает большинство ячеек без заполнения как имеющие что-то, и чтобы исправить это, мне нужно установить для каждой из них значение «Нет заполнения», чтобы это работало снова. Если я смогу придумать, как сделать видео, я сделаю это.

Apache MadMartigan Driver 30.05.2024 18:20
gifyu.com/image/SfJ6r
Apache MadMartigan Driver 30.05.2024 18:44
gifyu.com/image/SfJ6t
Apache MadMartigan Driver 30.05.2024 18:45

сделано и отправлено, письмо будет от RSI

Apache MadMartigan Driver 30.05.2024 19:15

Другие вопросы по теме