Как известно, в последних версиях Excel легко выделить дубликаты цветом, - для этого есть специальная опция в «условном форматировании».
Достаточно выделить диапазон, задать цвет заливки, - и все повторяющиеся (или, наоборот, уникальные) значения будут выделены.
Но иногда требуется, чтобы различные повторяющиеся значения были выделены РАЗНЫМИ ЦВЕТАМИ.
В этом случае, без макросов не обойтись.
Ниже приведён макрос, который как раз и решает эту задачу
(достаточно выделить диапазон ячеек, запустить макрос, - и повторяющиеся непустые ячейки получат одинаковый цвет заливки)
Sub ВыделитьДубликатыРазнымиЦветами() On Error Resume Next ' массив цветов, используемых для заливки ячеек-дубликатов Colors = Array(12900829, 15849925, 14408946, 14610923, 15986394, 14281213, 14277081, _ 9944516, 14994616, 12040422, 12379352, 15921906, 14336204, 15261367, 14281213) Dim coll As New Collection, dupes As New Collection, _ cols As New Collection, ra As Range, cell As Range, n& Err.Clear: Set ra = Intersect(Selection, ActiveSheet.UsedRange) If Err Then Exit Sub ra.Interior.ColorIndex = xlColorIndexNone: Application.ScreenUpdating = False For Each cell In ra.Cells ' запонимаем значение дубликатов в коллекции dupes Err.Clear: If Len(Trim(cell)) Then coll.Add CStr(cell.Value), CStr(cell.Value) If Err Then dupes.Add CStr(cell.Value), CStr(cell.Value) Next cell For i& = 1 To dupes.Count ' заполняем коллекцию cols цветами для разных дубликатов n = n Mod (UBound(Colors) + 1): cols.Add Colors(n), dupes(i): n = n + 1 Next For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет cell.Interior.color = cols(CStr(cell.Value)) Next cell Application.ScreenUpdating = True End Sub
Комментарии
Большое спасибо!!
Имеется поле суммы столбика В /=СУММ(B1:B23)/, можно сделать так, что эту сумму включались только поля, которые имеют определённый цвет другого поля? Например: при этом столбик А имеет цвет полей от А1 до А10 желтый, а с А11 до А23 красный.Т.Е. что бы автосумм учитывала только поля красного цвета, и при изменении его она пересчитывала результат!!!
Заранее спасибо!
Это гениально!!)спасибо
Огромное спасибо!
Евгений, там много в коде переделывать надо. Это только если под заказ (платно)
Отличный инструмент, спасибо!
Подскажите, что нужно изменить в этом макросе, чтоб закрашивались только те ячейки, которые повторяются не меньше 4-х (5-и, ..., 8-и) раз? Готов каждый раз лазить в макрос и менять на нужное кол-во, только подскажите что и где? (я не спец по макросам, к сожалению).
Огромное спасибо!
Добрый день .Есть большой лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Каждая ячейка содержит 10 цифр в порядке возрастания. Выглядят ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. некоторые ячейки из них с повторяющимися цифрами например: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99
4-5 цифр повторяются, остальные разные. Ячейки в которых совпадают все 10 цифр можно автоматически выделить с помощью условного форматирования.
А вот как сделать так, чтобы подобным образом автоматически выделялись цветом ячейки в каторых совпадают 4 цифры и более?
За раннее спасибо!
Александр, тут макрос нужно писать.
Можем сделать под заказ
Добрый день . Есть большой лист Excel. На нем есть отдельные ячейки с цифрами через запятую от 1 до 99. Каждая ячейка содержит 10 цифр в порядке возрастания. Выглядят ячейки так: 44,48,54,59,60,61,64,73,79,97; 23,32,35,38,41,56,62,63,65,84; и т.д. Но некоторые ячейки из них с повторяющимися цифрами например: 54,59,61,73,78,81,85,87,93,98; 48,54,59,60,64,68,72,77,85,92; 23,35,41,56,60,67,73,83,94,99
4-5 цифр повторяются, остальные разные. Ячейки в которых совпадают все 10 цифр можно автоматически выделить с помощью условного форматирования.
А вот как сделать так, чтобы подобным образом автоматически выделялись цветом ячейки в которых совпадают 4 цифры и более?
За раннее спасибо!
а где макрос? не могу скачать
Всё можно
Но это другом макрос нужен. Можем сделать под заказ
Подскажите, а можно ли сделать? Есть 2 столбца (первый составлен из второго с удалением дублей), мне нужно найти все дубли во втором и только то что дублируется окрасить в цвет и в первом и и во втором в один цвет?
Спасибо, супер макрос, очень помог
Де тут лайк ставить !? спасибо . главное что не режет глаза . единственное нужно убрать серый фон или сделать его потемнее . сливается с белым .
Спасибо!!!
Отлично!
Спасибо за Макрос, все работает и цвета приемлемые не режут глаз!!!
Благодарю за очень полезный макрос!
Добрый день. Спасибо за макрос!
Подскажите пожалуйста,как изменить макрос так, чтоб проверка осуществлялась только в нескольких столбцах и макрос запускался автоматически, при добавлении значения в строке?
Связано с тем, что вы изменили код, удалив первую строку On Error Resume Next
Добрый день! При запуск макроса происходит ошибка run-time 457 в блоке: coll.Add CStr(cell.Value), CStr(cell.Value)
С чем это может быть связано?
Спасибо, Вам, добрый человек! Так меня Ваш макрос выручил!
На данный момент через 56 цветов макрос использует ячейку без цвета а потом снова 56 цветов. Как так сделать что бы он не использовал ячейку без цвета?
Exel 2007
Приветствую!
У меня таблицы по 1000-2000 строк. Хочу увеличить количество и яркость цветов, скажите, пожалуйста, как это сделать? Точнее где взять код цветов?
Подскажите, а как сделать, что бы дубликаты подсвечивались по строке,а не по столбу.
Имею ввиду, что бы дубликаты в одном столбе не светились, а светились только дубликаты по строке
спасибо
Потому что, надо выбрать что-то одно. Если моим макросом пользуетесь, - зачем было условное форматирование использовать?
Макрос мой раскрасил ячейки, - только эта раскраска перекрывается сверху условным форматированием, - потому ничего не видно.
Добрый день!
подскажите пожалуйста почему не работает макрос(((( через форматирование выделила все повторяющиеся значения, вставила ваш макрос, выделила столбец, запустила макрос через Alt+F8 и ничего не изменилось....
Реально помог сегодня! Спасибо!!
Подскажите пож., какая кодировка цветов используется?
спасибо огромное,
вместе с
cell.EntireRow.Interior.Color = cols(CStr(cell.Value))
вообще нереально
Спасибо огромное за макрос!!Очень пригодился!!
Удалите строку Option Explicit
над макросом, — и всё заработает.
Здравствуйте! Скажите пжл, что не пыталась сделать макрос не работает, говорит что не задана переменная Colors, потом не задана i и т.п.
Приветствую!
У меня таблицы по 1000-2000 строк. Хочу увеличить количество и яркость цветов, скажите, пожалуйста, как это сделать? Точнее где взять код цветов?
В макросе прописаны коды цветов:
Colors = Array(12900829, 15849925, и т.д.
Цвета подобраны визуально отличающиеся, и неяркие.
Увеличьте количество значений в этом списке до 200, - разумеется, разных)
здравствуйте! Спасибо огромное за ценный макрос. Не могли бы вы подсказать как изменить макрос так, что бы было больше двухсот цветов для выделения?Заранее спасибо!
Здравствуйте, Денис.
Это в переводе значит «если ячейка непустая, то ...»
можно было записать иначе:
А что означает эта конструкция "If Len(Trim(cell)) Then" не могли бы прокомментировать?
Чтобы подсветить целиком строку, а не только ячейку, надо добавить EntireRow:
For Each cell In ra.Cells ' окрашиваем ячейки, если для её значения назначен цвет
cell.EntireRow.Interior.Color = cols(CStr(cell.Value))
Супер...Спасибо огромно...
Ваш сайт божественен! СПАСИБО! ОЧень помогли!
Отправить комментарий