asked on

Highlight Cell base on the color of another cell

Hello, I need to make some cells stand out based on the color of another cell. I've spent hours searching the internet for a solution to this problem. Responses for "color a cell based on the value of another cell" keep appearing in the responses. I am capable of doing it.
In my example, if the cells in Column A are pink (#FCD0D1), I need Columns G&H to turn pink as well. I discovered a formula, however I believe that it will only color the cells if there are two matches; anything less than two matches will not cause it to work.EE-Highlight Cell based on color of a different cell.xlsx

Martin Liss

You can do what you want by adding a little VBA code in your worksheet Change event. In this code anytime a change is made in column 'A' the color of the cells in columns G and H are updated.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngLastRow As Long
Dim cel As Range

lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If Not Intersect(Target, Columns("A")) Is Nothing Then
    Range("G3:H" & lngLastRow).Interior.Color = 16247773
    For Each cel In Columns("A").Cells
        If cel.DisplayFormat.Interior.Color = 14079484 Then
            Cells(cel.Row, "G").Interior.Color = 14079484
            Cells(cel.Row, "H").Interior.Color = 14079484
        End If
    Next
End If
        
End Sub

Open in new window

Audra Breedlove

ASKER

That's amazing. I've never seen a macro operate that quickly without me having to click anything.

Most of them were highlighted, but some weren't. Here is the full collection of data. I slightly modified your macro and placed my data in the columns and rows that were present in my project spreadsheet.

I filtered column B for pink, so that you can see the missing ones.

EE-Highlight Cell based on color of a different cell.xlsm

ASKER CERTIFIED SOLUTION

Martin Liss

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Martin Liss

You’re welcome, thanks for the testimonial and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you, including a new one concerning Easy Ways to do Hard Things.

Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015 and 2017
Experts Exchange Distinguished Expert in Excel 2018, 2021, 2022
Experts Exchange Distinguished Expert in Microsoft Office 2022
Experts Exchange Distinguished Expert in VBA 2022
Experts Exchange Top Expert VBA 2018 to 2022
Experts Exchange Top Expert Visual Basic Classic 2012 to 2022

Audra Breedlove

ASKER

I absolutely will.

byundt

I realize that the question has already been answered, but you really don't need VBA to do it.

Try a Conditional Formatting formula like this:

=AND($A3<>"",COUNTIF($A$3:$A$2867,$A3)>1)

Open in new window

EE-Highlight-Cell-based-on-color-of.xlsx

Rob Henson

Alternatively, what is the condition(s) that the cells in column A match to make them go pink?

Use that as a Conditional formatting and have the format applied to ranges in columns A, G & H

byundt

Column A turns pink based on a duplicate value criteria.

My suggested criteria for columns G and H mimics that by testing column A for non-blank cells and COUNTIF greater than 1.