MsgBox if any cell within range is blank

Hi,

This is my first post and I'm new to VBA. I'm trying to highlight any blank cells and display a message saying "there are incomplete fields" if any cells within a range are blank. I have created a button and managed to get the code to highlight any blank cells in yellow, I'm struggling to get the message box to only appear once, the best I've managed so far is it appears as the macro checks every cell. This is my current code:

Please Login or Register  to view this content.

This might confuse things further but I also have conditional formatting grey out some cells that don't require completion depending on what category is selected, I think the current code still colours those cells in yellow as they are left blank and then the conditional formatting just puts the grey over the top (so the yellow isn't visible). I would need the error message to only count blank cells that the conditional formatting doesn't grey out. Is that possible?

Thanks in advance

Sam

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

This might confuse things further but I also have conditional formatting grey out some cells that don't require completion depending on what category is selected, I think the current code still colours those cells in yellow as they are left blank and then the conditional formatting just puts the grey over the top (so the yellow isn't visible). I would need the error message to only count blank cells that the conditional formatting doesn't grey out. Is that possible?

The code can't detect whether a cell is colored due to conditional formatting. The only way to detect this in code is to use the same logic in the code that is used in the conditional formatting.

Can you attach your file?

Give this a try. Rather than a hidden sheet, I put the formulas into hidden columns on the same sheet.

If this works the way you want, then we can look at the code.

Another thought--it would be more effective to use CF to color blank cells yellow. If you do it in the code, then you have to run more code to clear the yellow. Using CF provides a way to prompt the user in real-time for what they need to fill in. Here is an update using that approach plus the code to check.

I assume that H15 and H16 would both be blank as soon as a new form is opened, so all those sections are gray before the user enters any data.

Here is a revision that turns blank cells yellow only when the button is clicked. Therefore any yellow will be cleared only the next time the button is clicked.

I added a ClearAllFill Sub that can be used for testing or for cleanup. This will clear the fill color in all the required cells. To ensure that it is using the same cells as the sub for the button click, I declared the Range string as a global constant at the top of the module. That way if you ever change the form, you just need to update the range in one place. (BTW the "=" in the Range string is unnecessary so I removed it.)