detect a color change in a cell
Posted on 2011-02-25
I have a spreadsheet where the users enter different color codes in cells that all mean different things. This is currently achieved by a userform with colored buttons that simply changes the color of the cell and then offsets the interior.colorindex value to another cell to be counted. An example of the code for this is:
' CellColor Macro
' Macro recorded 15/12/2005 by GordonT
.ColorIndex = 6
Selection.Font.ColorIndex = 1
Selection.Offset(0, 80).Value = 6
Selection.Borders.ColorIndex = 15
My problem is that sometimes people copy and paste cells without using the user interface or manually change the color of the cells. As a result, the colorindex value doesn't get written to the offset cell. As counting these offset values is crucial to the functionality of the spreadsheet I wondered if there was any way (perhaps in a worsheet change event) of automatically detecting that a cell's color has been changed and writing the new colorindex value to the offset cell?
I don't want to use a macro that simply scans the whole range and updates all the offset cell values accordingly - the spreadsheet covers the activities of 40 people for a whole year and a macro to do this takes 30 seconds to run, so, as you can imagine, doing this every time a cell is changed would be counterproductive. I literally want it to recognise that a single cell has changed and write the interior.colorindex value of that cell to the offset location.
Hope someone can help.