Help!! How to warn users that a cell is blank

[EM1987]

Hi,

I'm having a bit of trouble with writing a macro which identifies a blank cell within 3 'ranges' (C2:C4000; G2:4000; B2:B4000). I want to identfy a blank cell and for the macro to stop so that the user can make a change to the sheet, and then the macro to continue until the next blank cell within the range. But a error warning pops up (where the asterix appears in the code below) which states "Next without For". Please see code below:-


Sub BLANKCELLSWARNING()

Dim Target, cell As Range
Dim C, D, E As String
C = Range("C2:C4000")
D = Range("G2:G4000")
E = Range("B2:B4000")

Set Target = Sheets("Input").Range("C,D,E")
For Each cell In Target
If cell.Value = "" Then
MsgBox ("This cell is blank, fill in to continue")
*Next
End If
Exit Sub

End Sub

I'm not sure how to fix this, so any help with this would be much appreciated!! If there are any other issues with this please let me know (I'm pretty new to this, as you can tell!!)

Thanking you in advance,

EM1987

[ragulduy]

You don't have an end if, either put it all on one line

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

There are a few other issues as well, your definition of the range wouldn't work as you expect. you set C, D and E equal to the values of the range items as you have defined them as strings. You then assign the Range("C,D,E") which I think would just give you the entire columns. You can just do it as per the example below. I'm also not sure you want the brackets on the msgbox statement.
That said, I'm not sure this would really work as you are wanting. I don't think you would be able to edit the cells when the message box appears. I think you would need to end the macro each time to enable the user to change the cell. Also

Please Login or Register  to view this content.

Might be a slightly more efficient way of executing the loop.

[EM1987]

Fantastic, thanks for your help! Just one more question: is there a way to get the msgbox to appear at the end (i.e. after the checking has been done), and with the number of cells which have been changed?

[ragulduy]

Please Login or Register  to view this content.

[djdjdj]

If you want to change the value of the cell without stopping your macro, change this line:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

[EM1987]

That's perfect! Thanks for your help Yudlugar!!