Как придать цвет вкладке листа, если диапазон ячеек содержит текст

Question

Я пробовал код, который нашел на SE и в других местах, но они не работают так, как я думаю, могут. Я перечислю их ниже. Я почти уверен, что это простой вопрос.

Что я пытаюсь сделать: если в какой-либо из ячеек в диапазоне A2:A100 есть какой-либо текст или число, сделайте вкладку листа желтой. И мне нужно будет сделать это на более чем 20 вкладках.

Проблемы, которые у меня были с другим кодом: насколько я могу судить, они требуют редактирования ячейки, а затем быстро нажимают ввод снова. Я пытался пересчитать SHIFT + F9, но это не дало эффекта, так как я думаю, что это только для формул. Кажется, что код 1 работает, хотя приходится вручную вводить текст заново, но независимо от значения цвета, я всегда получаю черный цвет табуляции.

Код, который я пробовал:

Код 1:

Private Sub Worksheet_Change(ByVal Target As Range)
    MyVal = Range("A2:A27").Text

    With ActiveSheet.Tab
        Select Case MyVal
            Case ""
                .Color = xlColorIndexNone
            Case Else
                .ColorIndex = 6
        End Select
    End With
End Sub

Код 2: Это вопрос стекового потока , хотя я немного изменил код в соответствии со своими потребностями. В частности, если в заданном диапазоне нет значений, чтобы оставить цвет вкладки в покое, а в противном случае изменить его на значение цвета 6. Но я уверен, что сделал что-то не так, я не знаком с кодировкой VBA.

Private Sub Worksheet_Calculate()
    If Range("A2:A100").Text = "" Then
        ActiveWorkbook.ActiveSheet.Tab.Color = xlColorIndexNone
    Else
        ActiveWorkbook.ActiveSheet.Tab.Color = 6
    End If
End Sub

Спасибо за вашу помощь!

Answer 1

Вам может понадобиться использовать следующее событие:

Private Sub Worksheet_Change(ByVal Target As Range)

Вместо:

Worksheet_Calculate()

Если у вас есть код в Worksheet_Calculate , он может не выполняться, если лист не нуждается в пересчете (т. Е. В листе нет ячеек с формулами)

---

Вставьте это в окно кода листов, для которых вам нужно настроить это:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = ActiveSheet.Range("A2:A100")
    If Application.WorksheetFunction.CountBlank(myRange) = 99 Then
        ActiveWorkbook.ActiveSheet.Tab.Color = xlColorIndexNone
    Else
        ActiveWorkbook.ActiveSheet.Tab.Color = vbRed
    End If
End Sub

При этом используется функция COUNTBLANK которая описана здесь.

Вы также можете использовать код ниже:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myRange As Range
    Set myRange = ActiveSheet.Range("A2:A100")
    If Application.WorksheetFunction.CountA(myRange) = 0 Then
        ActiveWorkbook.ActiveSheet.Tab.Color = xlColorIndexNone
    Else
        ActiveWorkbook.ActiveSheet.Tab.Color = vbRed
    End If
End Sub

Это использует функцию COUNTA описанную здесь

---

COUNTBLANK и COUNTA могут показаться противоположными друг другу. Однако есть 2 различия, которые я заметил:

• COUNTBLANK считает ячейки, имеющие формулу, которая не возвращает никакого значения как пустое. COUNTA бы такую ячейку не пустой. Простой пример такой формулы: =IF(1=1,"","test") . Эта формула оценивает так, что она не возвращает никакого значения.

• COUNTA поддерживает несмежные диапазоны ячеек. COUNTBLANK нет.

---

Чтобы обновить цвета вкладок при открытой книге:

Вы можете иметь один и тот же код выше внутри ОБА функции Worksheet_Change и Worksheet_Calculate каждого раздела кода рабочего листа.

ИЛИ вы можете добавить следующее в окно кода рабочей книги:

Private Sub Workbook_Open()
    Call Sheet1.Worksheet_Change(ActiveSheet.Range("A1"))
    Call Sheet2.Worksheet_Change(ActiveSheet.Range("A1"))
    'Add lines of code for each sheet that you need the tab colors updated for. If you need tab colors updated for ALL sheets, you can loop through the sheets and call `Worksheet_Change` as well.
End Sub

Answer 2

В обоих случаях вы используете range.text который является строковой переменной, он дает вам только текст в первой ячейке вашего диапазона, вам нужен немного более сложный код. Если я хорошо понимаю, вы просто хотите проверить, не скрещены ли вы и не интересуетесь контентом. Вот несколько советов, чтобы установить ваши критерии:

• используйте функцию sheetheet для получения числа непустых ячеек: worksheetfunction.counta("A2:A100")>0

• или перейдите к следующей непустой ячейке в вашем диапазоне, используя метод end() .