Excel Macro to Highlight Cells based on "Key" Cell Color
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.
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.
Last Comment
ASKER
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
B C L
table1 color2 color1
table2 color2 color2
table3 color3 color3
table4 color1 color4
table5 color3
table6 color2
table7 color1
Let me see if I can give you some VBA code for that shortly.
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.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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.
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)
End If
Change the last line before the End Sub to these three:
If strMatchingcellList <> "" Then
Range(strMatchingcellList)
End If
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 :)
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 :)
ASKER
I had to make some changes. I couldn't get Worksheet_SelectionChange to fire, so I used Workbook_SheetSelectionCha
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!"
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!"
ASKER
Thanks!
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.
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.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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?