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

Я бы хотел:

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

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

 =GET.CELL(63,Sheet2!A1) 

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

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

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

#

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

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

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

Я бы хотел:

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

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

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

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

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

Вы должны:

  1. Определите изменение в Selection (для этого есть событие).
  2. Запросить цвет вашей исходной ячейки.
  3. При необходимости действуйте.

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

  1. Стандартный модуль (например, Module1). Вы должны сначала вставить модуль.
  2. ThisWorkbook.
  3. Лист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 

В Sheet2:

 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 

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

Заметки:

  1. Этот код не действует, если цвет исходной ячейки изменяется с vbBlue на другое. Вы ничего не указали для этого действия. Фактически, ваши спецификации были недостаточно подробными для охвата всех возможных случаев.

  2. Могут быть случаи (крайне маловероятные, я думаю), где этот код терпит неудачу. Например, если цвет изменяется с помощью другого кода VBA, без выбора / отмены выбора ячеек.

  3. Идея состоит в том, чтобы проверить необходимость действовать после максимально возможного количества соответствующих событий. Здесь я обнаруживаю Workbook_Open , Worksheet_Deactivate , Worksheet_SelectionChange . Вы можете добавить другие события с подходящими Sub , например Workbook_BeforeClose , Workbook_BeforeSave . Все это способ заменить несуществующее событие изменения формата ячейки.

  4. Мне нравится ответ pnuts (хотя у меня не было времени проверить его). Но настоящая дает гибкость, которая недоступна с другой. Могут быть некоторые случаи (в зависимости от того, что вам нужно сделать), которые не будут покрыты им.

  5. Существуют и другие возможные комбинации мест для определения объявления переменных и другого кода, в основном выполняющих одни и те же действия.

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

Тем не менее, вы задали вопрос «как», а не «почему нет», поэтому я предлагаю вам перейти от того места, где вы достигли, и выбрать «Листы1 А1» и «ДОМОЙ»> «Стили» – «Условное форматирование» – «Новое правило» … Используйте формулу, чтобы определить, какие ячейки для форматирования , форматирование значений, где эта формула истинна:

 =CellColor=23 

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

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

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


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

Выберите Sheet1 ColumnA и HOME> Стили – Условное форматирование, Новое правило …

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

 =Sheet2!A1>0.05 

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

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

Функция 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 

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

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

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

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

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

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

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

ОК?

Interesting Posts

Генерация цветов HEX в Excel

Как анализировать несколько листов в Excel для набора данных с помощью открытого xml

Ошибка выполнения «9». Подкласс вне диапазона при передаче в кодовом имени листа

Преобразование XSLT v1.0 сгруппировано с несколькими значениями, чтобы преобразовать экспорт Filemaker Pro XML в таблицу Excel

if в SQL для вычисления возраста

Создание резервной копии, защищенной паролем

OfficeJS getActiveViewAsync для Excel?

Как игнорировать ошибку и продолжить цикл?

Фактическая формула MS Excel для vdb ()

Используйте командную кнопку UserForm для перехода к определенной строке в модуле

Могу ли я контролировать физические размеры загруженной таблицы Excel?

Установите определенный цвет для легенды в динамической сводной диаграмме

Использовать формулу в пользовательском вычисленном поле в сводной таблице

Excel закрывается после времени бездействия пользователя

Удалить строки из листа, где совпадение найдено в excel

Давайте будем гением компьютера.