Audible and pop up notification on cell color change

Title sums it up pretty well. I have a file where one cell changes to red on error and I am hoping to have it generate a popup and sound when that happens. Not great with VBA but I found some sites where I created a "beepnow" script and a macro but I was only able to get it working on a formula and not a color change. Attached is a sample file, it is pretty simple. C10-19 are the source or original numbers, D10-19 is where you would validate against the C numbers. My team specifically uses this for serial numbers where we are validating from a list and we scan into the D columns. I am hoping I can get an error message to popup anytime D6 turns red to warn the person they scanned the wrong thing. The sample has some of the C columns populated and you will see that D13 does not match against any of the numbers in the C columns so you get a no-match and D6 changes to red.

If there is already some information here in the forum for this feel free to redirect me, I was unable to find much on the subject and most of it is older.

I am currently running O365.

Thanks!!!

PS - If anyone wants bragging rights, I would love it in the media validation columns B and E displayed nothing if the cells they relate to are blank. In the example file D14-D19 have no data but E14-E19 say no match until there is one.

You can't detect a color change, but since your color change is cause by conditional formatting, you can detect the condition you are using for CF.

This uses the Calculate event for the worksheet.

Please Login or Register  to view this content.

Bragging rights also claimed.

Originally Posted by Croweater

I beg to differ with this statement...

I should be more specific. There is no event for a formatting change. You have successfully solved this by detecting the CF color assigned, but this code does not detect a color change. Any time there is any change in column D, it detects if the color is currently red.

Good solution.

Originally Posted by 6StringJazzer

You have successfully solved this by detecting the CF color assigned, but this code does not detect a color change. Any time there is any change in column D, it detects if the color is currently red.

This is true, but while we are being pedantic, the solution you offered doesn't do anything logically different. It detects a difference in two cells. It will beep not necessarily on a colour 'change', but if a condition exists that causes that colour....exactly the same as mine.

The only difference is mine detects the colour, yours detects the condition that causes the colour.

Advantage of my solution... if another condition existed that caused the colour change, mine would still work.
Disadvantage...If you change the CF colour yours would still work.

Another difference, with your solution, anything that causes a re-calculation, whether it is related or not to the condition we want to bring the alert to, will cause the alert. This might be a distraction (PITA). Up to the OP from here.

I am not trying to debate which solution is better. In fact I thought your solution was very good and I gave you rep for it. I was clarifying my original point, which is just a matter of fact and not pedantic.

People often want code to run if all they do is manually change the color of a cell, and there is no event for that. When dealing with conditional formatting the situation is a bit different.

I used the more simple code supplied by 6StringJazzer though both worked equally well. Bragging rights go to maniac though, his formula correctly identified a no match condition in the validation column (e) where 6Strings formula just stayed blank but still provided the audible notification and popup. I too was only able to get it to stay blank, very happy to have a working solution.

Updated to solved and repped all 3 of you for usable solutions!!!

Thank you so very much and I apologize again for not monitoring the thread.

To mark a thread as solved in future, please use the solved tag rather than editing the thread title. I have amended this for you today.