У меня есть условное форматирование и 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.
ОК, смотрите мой ответ ниже.
Вам нужен способ исключить ячейки, у которых есть какая-либо существующая заливка, из правила условного форматирования «выделить строку».
Для этого вы можете добавить эту 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, чтобы включить то, что вы сделали... хотя все еще пытаюсь
Извините, мой ответ был более сложным, чем требовалось, отредактировано для упрощения.
И даже больше....
Раньше я думал, что у меня отличная смекалка....оказалось, что это всего лишь армейская смекалка, а этого очень не хватает. Большое спасибо за помощь в этом.
Приятно слышать, что все получается. Если это ответ на ваш вопрос, отметьте его как «Принято», чтобы помочь тем, кто придет позже с подобной проблемой.
Возможно, я слишком рано говорил о том, что все работает. Попробую кратко описать, что происходит.
Используя уже существующие CF и VBA в качестве отправной точки отсчета, когда я перемещаюсь по листу, строки и столбцы выделяются цветом (центральная точка - это любая активная ячейка), которую я добавил в модуль, и нет никаких изменений, которые, я считаю, правильно, потому что еще нет другой ссылки (CF) на «NoFill». Однако, когда я делаю изменение CF, он рассматривает большинство ячеек без заполнения как имеющие что-то, и чтобы исправить это, мне нужно установить для каждой из них значение «Нет заполнения», чтобы это работало снова. Если я смогу придумать, как сделать видео, я сделаю это.
сделано и отправлено, письмо будет от RSI
«Я следовал инструкциям» — каким инструкциям? CF всегда имеет приоритет над существующим форматированием, поэтому я не думаю, что вы сможете заставить его игнорировать ячейки с существующей заливкой, но вы можете (например) исключить определенные столбцы из CF.