Установите цвет одной ячейки на основе цвета другой ячейки

Question

Установите цвет одной ячейки на основе цвета другой ячейки

То, что я хотел бы иметь:

IF   A1 in Sheet 2 is blue  
Then A1 in Sheet 1 changes to blue

Я знаю, что могу получить цвет А1 на листе 2, используя:

=GET.CELL(63,Sheet2!A1)

( Excel: Могу ли я создать условную формулу на основе цвета ячейки?)

Но я не могу понять, что мне делать на следующем шаге.

Какие-либо предложения?

#

Обновление от 12.01.2015 Извините, если я дал слишком мало информации о своем деле и не указал в начале четко, хочу ли я сделать это в VBA или нет. Вначале я думал, что функция будет работать, но, рассматривая мой файл, функция может вообще не работать.

Речь идет о результатах анализа корреляции из SPSS, есть три столбца: коэффициент корреляции, p-значение и размер выборки. Мне нужно проверить коэффициент и значение p одновременно, и представить коэффициент в удобочитаемом виде. Скажем, у меня есть корреляция между 50 переменными и 100 переменными, я бы не вставлял коэффициент и значение p на одном листе, а скорее:

лист один: коэффициент лист два: значение р

Что я хотел бы иметь:

Если значение p больше 0,05, коэффициент (ячейка) меняется на синий / синий или черный.

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

Я попробую все предложения ниже и сообщу об этом позже.

1

excel vba excel-vba format xlm

Источник

user4213622 07 янв '15 в 20:55

4 ответа

Другие вопросы по тегам excel vba excel-vba format xlm

user2707864 08 янв '15 в 14:09 2015-01-08 14:09 · Answer 1 · 2015-01-08 14:09

Что вам нужно, это способ обнаружения изменений в формате ячейки. Похоже, что нет никакого события, которое срабатывает при изменении формата. См. Как обнаружить изменения в формате ячейки?

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

Ты должен:

Обнаружить изменение в выделении (для этого есть событие).
Узнайте о цвете вашей исходной ячейки.
Действуй, если нужно.

Перейдите в редактор Visual Basic (VBE) и добавьте код в три модуля:

Стандартный модуль (скажем, Module1). Вы должны сначала вставить модуль.
ThisWorkbook.
Лист2.

В модуле 1:

Public prev_sel As Range
Public wssrc As Worksheet, wstrg As Worksheet
Public ssrc As String, strg As String
Public rngsrc As Range, rngtrg As Range

Sub copy_color(rngs As Range, rngt As Range)
    Dim csrc As Long
    csrc = rngs.Interior.Color
    If (csrc = vbBlue) Then
        rngt.Interior.Color = vbBlue
    End If
End Sub

Sub copy_color2(rngs As Range, rngt As Range)
    If (TypeName(prev_sel) = "Range") Then
        Dim pss As String
        pss = prev_sel.Parent.Name
        If (pss = ssrc) Then
            Dim ints As Range
            Set ints = Application.Intersect(rngs, prev_sel)
            If (Not (ints Is Nothing)) Then
                Call copy_color(rngs, rngt)
            End If
        End If
    End If
End Sub

В этой рабочей книге:

Private Sub Workbook_Open()
    ssrc = "Sheet2"
    strg = "Sheet1"
    Set wssrc = Worksheets(ssrc)
    Set wstrg = Worksheets(strg)
    Set rngsrc = wssrc.Range("A1")
    Set rngtrg = wstrg.Range("A1")
    Call copy_color(rngsrc, rngtrg)

    If (TypeName(Selection) = "Range") Then
        Set prev_sel = Selection
    Else
        Set prev_sel = Nothing
    End If
End Sub

В листе 2:

Private Sub Worksheet_Deactivate()
    Call copy_color(rngsrc, rngtrg)
    If (TypeName(Selection) = "Range") Then
        Set prev_sel = Selection
    Else
        Set prev_sel = Nothing
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call copy_color2(rngsrc, rngtrg)
    If (TypeName(Target) = "Range") Then
        Set prev_sel = Target
    End If
End Sub

Я скоро отредактирую с объяснениями. Читая внимательно, это легко понять.

Заметки:

Этот код не действует, если цвет исходной ячейки изменяется с vbBlue к чему-то еще. Вы не указали ничего для этого действия. На самом деле, ваша спецификация не была достаточно подробной, чтобы охватить все возможные случаи.
Могут быть случаи (крайне маловероятно, я думаю), когда этот код не работает. Например, если цвет изменяется с помощью другого кода VBA, без выбора / отмены выбора ячеек.
Идея состоит в том, чтобы проверить необходимость действовать после как можно большего количества соответствующих событий. Здесь я обнаруживаю Workbook_Open, Worksheet_Deactivate, Worksheet_SelectionChange, Вы можете добавить другие события с подходящими Subs, например, Workbook_BeforeClose, Workbook_BeforeSave, Все это способ заменить несуществующее событие изменения формата ячейки.
Мне нравится ответ по pnuts (хотя я не успел его проверить). Но настоящее дает гибкость, которая не доступна с другим. Там могут быть некоторые случаи (в зависимости от того, что вам нужно сделать), которые не будут покрыты этим.
Существуют и другие возможные комбинации мест, где можно найти объявление переменных и другой код, по сути, выполняющий те же действия.

user1505120 08 янв '15 в 02:31 2015-01-08 02:31 · Answer 2 · 2015-01-08 02:31

Не рекомендуется из-за использования макропрограммы XLM (не XML) GET.CELL. Это из технологии, представленной 30 лет назад, которая была фактически заменена восемь лет спустя. Теперь, когда почти все его элементы перестали существовать, можно ожидать, что у немногих оставшихся будет средняя продолжительность жизни. Microsoft поощряет переход на VBA.

Тем не менее, вы спросили "как", а не "почему нет", поэтому я предлагаю вам перейти от того, где вы достигли, и выбрать Sheet1 A1 и HOME> Стили - Условное форматирование - Новое правило..., используйте формулу, чтобы определить, какие ячейки отформатировать, Форматировать значения, где эта формула верна:

=CellColor=23

и выберите синее форматирование по вашему выбору, ОК, ОК, Применить.

23 это довольно стандартное число для Blue (не Light, не Dark), но ваша конфигурация может ожидать другое число.

Обратите внимание, что другим недостатком является то, что, в отличие от CF в целом, ответ не является автоматическим - вам может потребоваться ввести что-то в Sheet1 A1 или Shift + F9, чтобы вызвать обновление.

Если ваши данные распределены по двум листам (Sheet1 и Sheet2, оба ColumnA) и существует соотношение 1: 1 (p-значение в A1 для Sheet2 соответствует коэффициенту корреляции в A1 для Sheet1), тогда простое правило условного форматирования может хватить:

Выберите Sheet1 ColumnA и HOME > Styles - Условное форматирование, Новое правило...

Используйте формулу, чтобы определить, какие ячейки форматировать
Отформатируйте значения, где эта формула верна:

=Sheet2!A1>0.05

Формат..., выберите темно-синий или подходящий, ОК, ОК.

Это же правило может быть применено в Sheet2 (ColumnA) таким же образом, поэтому ячейки (по строкам), условно отформатированные на одном листе, являются ячейками, условно отформатированными на другом.

user4413676 08 янв '15 в 17:28 2015-01-08 17:28 · Answer 3 · 2015-01-08 17:28

Для ясности и простоты предоставляемых вами функций вы можете использовать условное форматирование и выбрать форматирование цветом. Это невероятно легко, если вы знаете, как это сделать. Основной трюк заключается в том, какую формулу вводить и, в частности, в какую ячейку требуется формула условных форматов для ссылки, когда условный формат применяется к многосотовому диапазону.

В качестве примера. Если ваше правило условного форматирования создано таким образом, что оно применяется к диапазону $C$5:$C$10, используемую вами формулу часто нужно вводить как =(A5="A"). Обратите внимание, что это относительная формула адресации, т.е. без знака доллара. это дает эффект ячейки с6, проверяющей значение а6 и т. д.

Ваша единственная сложность сейчас заключается в проверке форматирования ячейки, а не значения, которое она хранит. В 2013 году вы все еще можете использовать =GET.CELL(63,A5) чтобы сделать это, однако это не может быть введено в формулу правила CF... Другие посты обсуждают причины и причины использования этого. Смотрите эту ссылку, которая описала, как получить информацию о ячейке.

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

Вы должны использовать следующую формулу в именованном диапазоне под названием "Get . =GET.CELL(65,OFFSET(INDIRECT("RC",FALSE),0,1))

У меня это работает, и ключевую информацию можно найти на одной из указанных веб-страниц.

ОК?

user2474149 07 янв '15 в 21:44 2015-01-07 21:44 · Answer 4 · 2015-01-07 21:44

Функция GET.CELL, хотя и полезна, происходит от старого макроязыка XLM, который использовался до VBA. При этом вы можете столкнуться с ограничениями, например, в то время Excel использовал ограниченное количество цветов (я читал где-то около 60?).

В качестве альтернативы, с небольшим количеством VBA, вы можете поэкспериментировать с Внутренним объектом, а также с Объектом шрифта:

Sheets("Sheet1").Range("A1").Interior.Color = vbBlue
Sheets("Sheet1").Range("A1").Font.Color = vbYellow
    If Sheets("Sheet1").Range("A1").Interior.Color = vbBlue Then _
    Sheets("Sheet2").Range("A1").Interior.Color = vbBlue
    If Sheets("Sheet1").Range("A1").Font.Color = vbYellow Then _
    Sheets("Sheet2").Range("A1").Font.Color = vbYellow

Скорее всего, вам придется изучить различные способы задания цветов, чтобы использовать их для максимального контроля / гибкости.