Detecting Color change in Excel cell

[000Steve000]

I would like to run a macro to check the contents of a cell when a user chages its colour. The colour change does not trigger the worksheet change event. Can anyone help?

 

[VBAjedi]

I think your options are going to fall into two general categories:

1) Let your validation check be triggered by an unrelated event (maybe Selection_Change, or maybe just a timed check every 10 seconds, etc)

2) Design the spreadsheet in a way that forces the user to do something that triggers the change event for that cell.

Which approach would you be most interested in?

VBAjedi

 

[000Steve000]

The 2nd option sounds the most promising. Maybe removing built -in background colour toolbar function and replacing it with one of my own. I can't think what else you might have had in mind.

 

[SkipVought]

Hi,

Here's how it's done with with selecion change.

You store the ADDRESS of the PREVIOUS & CURRENT SELECTION and the corresponding PROPERTIES of those selections that you want to test in the SelectionChange event and then test the current state against the previous state. Keep in mind that multiple cells can be selected and changed.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[000Steve000]

Skip,

Thanks for you response, I understand the reasoning, but how can I pass the previous cell's location to the Selection Change when the Target parameter holds only the new location.

 

[VBAjedi]

For the second option, what I had in mind was providing a button, floating toolbar button, or key combination that the user presses to flag/color a cell. All of these will allow you to easily append code that does your data check.

Another similar option would be to train them to right-click the cell they want to flag/highlight. Then use the BeforeRightclick event like this:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
x = MsgBox("Flag/Color this cell?", vbYesNo)
If x = 6 Then ' user chose Yes
   ' Do your thing here
   Cancel = True ' Prevents right-click menu
Else
   ' Do nothing
End If
End Sub

Hope that helps!

VBAjedi

 

[000Steve000]

Thanks jedi (nice name!), but Skip's way allows the users to select the colours in the same way as they always have. I have now managed to keep track of the old cell location in the changeselection event using a public variable. I can then call the change event using the old location and check for the color change. Cheers guys.

 

[SkipVought]

You could, for instance, designate a sheet for these values. Make the sheet visible property xlsheetveryhidden. Name on cell Prev and another This

When you enter the selection change event, [Prev] = [This] and [This] = Target.Address

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]