LinkBack URL
About LinkBacks
Hello! I am trying to write a macro that'll prevent an excel workbook from being saved if any of the cells in the active sheet are filled with a certain color. Can anyone help me out? thank you!
Hello! I am trying to write a macro that'll prevent an excel workbook from being saved if any of the cells in the active sheet are filled with a certain color. Can anyone help me out? thank you!
Place this macro in the code module for ThisWorkbook. It uses the ColorIndex number 3 which is red. Change the number to suit your needs. It will detect only the first cell that is color filled. If you want to detect all of them, delete the 'Exit Sub' line. If you do this and you have many that are color filled, you will get a warning for each cell. It also will prevent the workbook from being closed if any cell is color filled otherwise the file could be closed without saving it and you would lose any changes made to the data.
Please Login or Register to view this content.
Last edited by Mumps1; 01-29-2014 at 12:55 PM.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
Thank you for your reply! I've copied this into my workbook code module and it doesn't seem to be working. I have the color fill happening by conditional formatting, and I have another BeforeSave event happening in my code already. Would either of these things be causing an issue?
Conditional formatting does present a problem. Provided that you have only one conditional formatting rule, the following should work. Again, you may have to change the "255" (RGB red) to suit your needs.
Please Login or Register to view this content.
I'm not sure what you mean by only one conditional formatting rule. I have 5 or 6 rules applied to the sheet but they all result in the same conditional format of a red fill in the cell. I would like the user to be alerted if any of these are triggered. When I copied and pasted your code I got the following error: Run-time error '9': Subscript out of range. I'm not that familiar with VBA to trouble shoot it. Is this happening because I already have code written for a separate before save event? thanks again for your time, much appreciated.
I'm not sure what is happening. Picking up conditional formatting is tricky. I can't promise a solution but could you post a copy of your file and I'll have a look. If it contains any confidential information, you could replace it with generic data.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks