Solved

detect a color change in a cell

Posted on 2011-02-25
15
424 Views
Last Modified: 2012-05-11
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:

Sub ColorYellow()
'
' CellColor Macro
' Macro recorded 15/12/2005 by GordonT
'

'
    With Selection.Interior
        .ColorIndex = 6
        Selection.Font.ColorIndex = 1
        Selection.Offset(0, 80).Value = 6
    End With
    Selection.Borders.ColorIndex = 15
    End Sub

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.

Regards

Terry
0
Comment
Question by:Terrygordon
  • 5
  • 5
  • 4
  • +1
15 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34979825
No, there is no event for that, so you would have to scan the whole range. You would be better off using code to colour the data based on a value they enter rather than trying to do this the other way round. Colour is not data, and should not be used as such. ;)
0
 

Author Comment

by:Terrygordon
ID: 34979947
Unfortunately the values they enter in the cells are quite random but, again, essential to the functionality of the spreadsheet so that is not really an option.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34979965
Then your options are to scan the whole range, or use volatile UDFs to return the colorindex of the range in question (though they will only be recalculated when the workbook calculates, not when the colour is actually changed.
0
 
LVL 17

Expert Comment

by:gtgloner
ID: 34981373
Not sure if this is any use to you, but here is a UDF that sums the values of cells based on the background colour/font colour. You might be able to adapt it to your purpose.
Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
    Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
' You can call this function from a worksheet cell with a formula like:
' =SUMBYCOLOR(A1:A10,3,FALSE)

Dim rng As Range
Dim OK As Boolean

Application.Volatile True
For Each rng In InRange.Cells
    If OfText = True Then
        OK = (rng.Font.ColorIndex = WhatColorIndex)
    Else
        OK = (rng.Interior.ColorIndex = WhatColorIndex)
    End If
    If OK And IsNumeric(rng.Value) Then
        SumByColor = SumByColor + rng.Value
    End If
Next rng

End Function

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
ID: 34985218
I think I may have worked out a solution for you.

This processes on every worksheet_selectionChange() event.  It works on one cell (haven't tested copy/paste, but SHOULD be able to be adapted) - let's see if this is helping, and I can assist further, if needed.

On a selection change (clicking or arrowing anywhere in the worksheet), the Worksheet_SelectionChange() event is fired.  The routine does the following:

1 - turns events off - because its going to do an UNDO and we don't want a recursive loop
2 - after the undo, the activecell is now where it WAS before the selection change, and the color of the cell is what it WAS before the change, if any.
3 - it captures the priorCell address, and gets the priorColor
4 - then UNDO is done again - this is effectively a REDO
5 - now, we can get the most recent color - currentColor from that range priorCell
6 - now we need to clear the UNDO stack, so we can go about our business and move forward with spreadsheet work!
7 - so, then we reposition the cursor back to the cell where the user was maneouvering - the Target - Target.select gets us there
8 - finally, we check to see if currentColor is different from priorColor, and if so, you can put your offset logic there (it currently does a msgbox popup letting you know it trapped the color change event!)
9 - once you've made the offset change (see code below), then need to enable events back to true for normal processing.
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim priorColor As Long
Dim currentColor As Long
Dim myPriorCell As Range


Application.EnableEvents = False
On Error GoTo endcheck 'nothing in the stack, so nothing to process

    'find last cell selected and color before change
    Application.Undo 'now we're back at the last active cell, and the before color is present
    Set myPriorCell = ActiveCell
    priorColor = myPriorCell.Interior.Color
    
    'revert back and get color from last selected cell after change, if any
    Application.Undo 'now the active cell is back where it was, and the color of the prior active cell is what it was most recently changed to
    currentColor = myPriorCell.Interior.Color
    
    'clear undo stack
    Range("A1").Copy Range("A1")

    'now get cursor back to where it was headed
    Target.Select
    
    If currentColor <> priorColor Then
        'PUT OFFSET CODE LOGIC HERE - priorCell is the range of the changed color, priorColor it its old color, currentColor is its new color
        MsgBox "colors were changed, need to set offset logic", vbOKOnly
    End If

endcheck:
Application.EnableEvents = True

End Sub

Open in new window


I hope this helps!

Demo file is attached.

Dave

ColorChange-Event-r1.xlsm
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34986332
Does that not assume they only change one cell at a time?
0
 

Author Comment

by:Terrygordon
ID: 34986783
Hi Dave

I am trying your code, which works fine on the sample sheet you provided. But in my workbook, it kept on going back to a cell on the last sheet I was on. I think this was a minor glitch because when I closed and reopened the workbook (on the correct sheet) it worked fine. I can put some code in the workbook open event to ensure it always opens on this sheet.

Incidentally, it seems to work ok when a cell is copy/pasted as well.

The only problem is that it does only work on one cell at a time and my users will often copy ranges of cells so is there any way that it can cope with a range of cells being changed?

Regards

Terry
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 41

Expert Comment

by:dlmille
ID: 34986873
@rorya and Terry - yes, I stated that in my post, this works on one cell at a time.  I wasn't going to over work it unless it was directionally what Terry was looking for...

I'll take a look now at managing range changes...

Dave
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34986953
FWIW, I'd use functions to get the colorindex rather than event code because this will prevent your users undoing anything, and they tend not to be happy about that sort of thing.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34987050
@rorya - read the fine print... the UNDO is not permanent, the function REDOes the change, lol.  However, the rest of the stack is busted, as would any stack be busted once a macro performs any changes in the worksheet.  And unless something can be made of the sumofcolor suggestion or other alternative, this should suit as the best alternative (did i just say if there is no better alternative then this is the best? lol!)

Here's the adapted function.  It handles single cell changes, multiple cell changes, or mass copy/paste changes.  To demonstrate how it works with a range of input as a result of a change, I also had it color the cells in the same row of column U as an example of what you might be trying to do.

As far as the starting sheet is concerned, I've circumvented that, but I bust the undo stack doing that.  So if you're in another sheet, even at the start, then go to this sheet with the color event, the UNDO stack is busted (so it doesn't revert back on an UNDO trying to see if there were changes).

Enjoy!

Dave
ColorChange-Event-r2.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34987160
As the macro does change values in the workbook, the UNDO stack is still purged - again, this happens on ANY macro that makes sheet changes, so if you have those running around, or you're happy with this solution, then you'll just need to explain it to your users...

I've given thought as well to the sumofcolor routine and it may also work for you depending on how your sheet is  structured.  If you MUST make updates 80 columns to the right of their change, then this event will work for you.  I'm a bit boggled to understand how sumofcolor would work in that instance, but then I haven't thought THAT much about it - seems like you'd have to have prescience to understand exactly where to land that function!

I modified this code to do exactly that - wherever you change the color, the color will also be duplicated at 80 columns to the right (no error checking going out of bounds to the right :)

Cheers,

Dave
ColorChange-Event-r4.xlsm
0
 

Author Comment

by:Terrygordon
ID: 34988095
Hi Dave

This seems to be doing the trick but I think I'm confused about the variable that needs to be offset. Basically,

myPriorCell.Offset(0, 30).Value = currentColor

doesn't seem to work (it did in the single cell version)

So how do I offset the colorindex numbers for the changed range so that if, for example, the colorindex values of the cells changed from 3 to 6, 6 would appear in the offset cell?

Thanks in advance (and for a great job that supposedly couldn't be done).

Regards

Terry
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 34988452
I love "it can't be done" challenges, because just about everything can - but at what cost.  This one, not too bad and I learned something in the process...

I assume you're using version r4?  In the code, yes I noticed I didn't update the remarks - its updated in the following:
 
For i = 0 To UBound(myColorInfo) 'the bounds will be the same on prior or current, as it was the same selection
        If myColorInfo(i).currentColor <> myColorInfo(i).priorColor Then
            'PUT OFFSET CODE LOGIC HERE - 
            myColorInfo(i).rng.Offset(0, 80).Interior.Color = myColorInfo(i).rng.Interior.Color ' <<<--THIS IS WHERE YOU PUT YOUR CODE AND HOW YOU ADDRESS EACH CELL IN A RANGE THAT HAS BEEN CHANGED
        End If
    Next i

Open in new window


So, you're not using myPriorCell, you're using myColorInfo(i) which is a structure that contains priorColor, currentColor, and rng.  You address them as myColorInfo(i).priorColor, myColorInfo(i).currentColor, and myColorInfo(i).rng

the .rng element (kind of like "property" but not) of the data structure holds the range, os you can offset that with the .Offset method.

So.. if myColorInfo(i).priorColor = 3, and myColorInfo(i).currentColor = 6, then

myColorInfo(i).rng.offset(0,80).interior.color = myColorinfo(i).currentColor, which would be the same as

myColorInfo(i).rng.offset(0,80).interior.color = myColorInfo(i).rng.interior.color (since currentColor is the same color as what's at the current .rng)...

I'm not sure it matters, but I changed all the interior.color statements to interior.colorindex to align with your lingo.

Let me know if this helps!

PS download the latest file r5 here, I updated to align the documentation and these examples discussed...

Dave
ColorChange-Event-r5.xlsm
0
 

Author Comment

by:Terrygordon
ID: 34988683
Hi Dave

Thanks mate. Pure genius. I changed the offset line to:

myColorInfo(i).rng.Offset(0, 30).Value = myColorInfo(i).rng.Interior.ColorIndex

and it works like a dream.

Thanks again.

Regards

Terry
0
 

Author Closing Comment

by:Terrygordon
ID: 34988687
Just proves that 'impossible' is a very subjective concept :-)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now