Highlight blank cells after Worksheet Change Event

Hi
I have a Worksheet Change Event which moves the cursor through a specific range of cells which the user needs to complete, I'm looking for some coding to highlight any blank cells after the Change Event has ran, something like yellow fill and a message box to request the user completes all highlighted cells

My cell range is quite random rather than being a full column, a section of it being E5,E7,H5,H7,L5,L7,J11

I've tried various options found online however can't get anything to work, my VBA knowledge is pretty limited I'm afraid

Any thoughts anyone?

Cheers

Twaddy

There are better ways to do what you are proposing, however, since it sounds like you have already invested time into your layout I will answer your direct question. At the end of your Change Event call the following procedure. Add ranges into the array to match what you need to check.

Please Login or Register  to view this content.

Note: Be careful using Change_Event. Make sure you have solidified it so it does not trigger when you do not want it to.

Hi stnkynts

Thanks for your reply, in hindsight a User Form would probably have better suited my needs. I've added your code to a module and called it from the ChangeEvent however I'm getting a Run time error message of 'Application-defined or object-defined error!

When I debug it highlights this element of the code

Please Login or Register  to view this content.

, although I've just ran it again and it's debugged at

Please Login or Register  to view this content.

this time

On the first error I'd entered a value in the first cell, the 2nd instance I'd left the first cell blank and gone to enter a value in the 2nd cell

Any thoughts?

Many thanks

Twaddy

Hang fire, think it may be as simple as not adding code to remove protection, my bad

Hi
I've added code to sort the protection so no longer receiving the run time error, however it isn't quite doing what I'm looking for

I've added an entry in the first specified cell which has immediately turned the 2nd and 3rd cells yellow, I'm looking for the check to only run once the user has been through all the cells, is that likely to be due to when I'm calling for the 'Highlight_Blanks' code which is currently at the end of the Change Event?

Cheers

Twaddy

is that likely to be due to when I'm calling for the 'Highlight_Blanks' code which is currently at the end of the Change Event?

Correct. By having it called from the Change Event, see my note in previous post, you are triggering it whenever the parameter within you change_event are true, which I guess is not when you want it. You can easily correct this by altering when the code is run within the change event or just running it manually as its own procedure.

Thanks for your help stnkynts, all sorted now