Excel Macro to Highlight Cells based on "Key" Cell Color

Doug Bishop
Doug Bishop used Ask the Experts™
on
I hate being color-blind. I hate even more when people who aren't think the whole world isn't. I've recently started a new position and the previous developer designed an Excel sheet that has a 'key' column (Column L). This column has 18 rows, each cell having a different color, with a 'description' in the column to its right (column M). Column B has many table names, and column C is an empty cell with only a fill color, that corresponds to one of the 18 colors in Column L. I can't for the life of me decipher this.

I would like to write a macro that works two ways; when I click on one of the color-coded cells in column L, all cells in column B that have a matching fill color in column C will be selected. So, if I click on the cell in column L that has a light-blue fill color, all cells in column B that have a corresponding light-blue fill color in column C will be selected.

Likewise, if I click any table name (cell in column B) or its corresponding fill color in column C, the appropriate cell in column L will become selected.

(fyi, column A is an empty column with no data and other columns between C and L have various comment data and are of no interest).

I am not even sure it is possible to code events to a cell click, or how to go about this, so I am reaching out for assistance from the Excel macro gurus.

Thanks.

p.s. a potential solution might be to have a button on the sheet that I can press. If the active cell is in column L then select all cells in column B that macth the fill color of the active cell in column L. If the active cell is in columns B or C, then select the cell in column L that macthes the fill color of the cell in column C (active cell if the active cell is in column C, or fill color using an offest of +1 for the column if the active cell is in column B). This would work if Excel cannot act on a click event of a cell.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Color in sheets is a wonderful thing if used properly (which includes using it for the right audience, which this was not.)

A macro could be created so that when a cell changes, it can look inside a specific range of cells and select those with matching colors (is that background or foreground colors, or both?)
Is that what you're looking for?
Doug BishopDatabase Developer

Author

Commented:
No, I am looking for a way to identify all cells with a specific color, or identify the 'key' associated with a specific cell. Below represents a simplified example. If I click the cell representing table5, or the color to its right (color3), then color3 in column L would become a "selected" cell. Likewise, if I click the cell in column L representing colr3, then the cells representing table3 and table5 would become "selected." Each color in column L is represented only once in that column, but can reference multiple rows in column C (thus the represented table in column b). Note that columns C and L do not actually have text in them, they just have the fill colors that make up the keys.


B            C                        L
table1      color2                  color1
table2      color2                  color2
table3      color3                  color3
table4      color1                  color4
table5      color3
table6      color2
table7      color1

Commented:
Let me see if I can give you some VBA code for that shortly.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Steven CarnahanAssistant Vice President\Network Manager

Commented:
OH MY!  dbbishop: I never thought about something like this. I totally agree with the color blind thing as I too enjoy the benefits of this condition.  (St. Patricks day being one of them -LOL)  I am not even going to attempt a solution to this. I will wait for rspahitz and see what happens.  
Commented:
OK try this code in the code area of the current sheet.  (You can get there with Alt-F11 then double-click on the sheet listed in the panel near the top-left corner.

 
Private mbSelecting As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not mbSelecting Then
        mbSelecting = True
        Select Case Target.Column
            Case 2, 3 ' column B or C
                SelectCellsBasedOnColor Cells(Target.Row, 3).Interior.Color
            Case 12 ' column L
                SelectCellsBasedOnColor Target.Interior.Color
        End Select
        mbSelecting = False
    End If
End Sub

Private Sub SelectCellsBasedOnColor(SourceColor As Long)
    Dim strMatchingcellList As String
    Dim objCell As Range
    
    strMatchingcellList = ""
    
    For Each objCell In Range("C:C")
        If objCell.Interior.Color = vbWhite Then
            Exit For
        End If
        Debug.Print objCell.Address
        If objCell.Interior.Color = SourceColor Then
            strMatchingcellList = strMatchingcellList & "," & objCell.Address
        End If
    Next objCell
    
    For Each objCell In Range("L:L")
        If objCell.Interior.Color = vbWhite Then
            Exit For
        End If
        Debug.Print objCell.Address
        If objCell.Interior.Color = SourceColor Then
            strMatchingcellList = strMatchingcellList & "," & objCell.Address
        End If
    Next objCell
    
    strMatchingcellList = Mid(strMatchingcellList, 2) ' remove leading comma
    Range(strMatchingcellList).Select
    
End Sub

Open in new window

Commented:
Oh, at this point, my assumption is that white background is considered a non-item so as it goes down the list of items in column C and L, if it sees a white background it stops and selects what it found so far (if any.)

One side note: you may want to add a function that you can add to column C so that you can see the internal code number for the backgorund color of the cell.  I'm not sure if you want the same for column L.
Alternately, we could probably write something to convert the colors into colors plus various shading, which Excel uses when printing to black&white printers.  That should help tremendously.

Commented:
Minor mistake (so you can click other cells with nothing in them.

Change the last line before the End Sub to these three:


    If strMatchingcellList <> "" Then
        Range(strMatchingcellList).Select
    End If

Commented:
FYI
Using the above, you will then have all of the cells selected for a given color.  You can then apply a background pattern of your choice (using Format Cells | Cells | Pattern Style)
Apply different patterns/shades to each color so you can differentiate then and you won't need my code any more :)
Doug BishopDatabase Developer

Author

Commented:
I had to make some changes. I couldn't get Worksheet_SelectionChange to fire, so I used Workbook_SheetSelectionChange. That worked out fairly well since the workbook contains several sheets and I am only interested in one, and the sheet object is passed, thus I can test for the sheet name before I even run additional code.

I also changed the code in SelectCellsBasedOnColor. If I click on a cell in column 2 or 3, I only want to highlight the matching color in column 12. Your code was highlighting all similar colors in column 3. I only want to do that if I click a color in column 12. I also applied an offset so the text (to the right of the key in column 12, or the left of the key in column 3) would be selected and not the actual color itself.

Not certain I could come up with 18 distinct colors that I could differentiate between. I can tell differences between adjacent cells (e.g. L8 and L9) but if they are close and separated by a couple of cells (let alone 10 columns and 30 rows), forget it!!! I have two colors in coloumn 12 that are about 4 cells apart, and to me they look identical. A co-worker looked at them and said, "You've got to be kidding me!"
Doug BishopDatabase Developer

Author

Commented:
Thanks!

Commented:
Interesting challenge.  Glad you were able to work out all the kinks to match your requirements.

FYI
In my opinion, color should be used to ENHANCE projects, not to act as reporting tools as used here.  As mentioned, aside from the color-blind people, there are also the color-blind printers and if you print a report (or view it on a monitor that is not working quite right with it's color beams) then it shouldn't stop you from continuing your work.  
Hopefully the next project you work with will take this into consideration.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial