I have a spread sheet in which ther

[bnageshrao]

I have a spread sheet in which there are 999 rows.All of the cells in a row is a required field except one. Can we write a macro to check for empty cells as soon as the user finishes inputting a row and if there are any required cells left blank then to highlight all those blank cells so that user can quickly input those values.

 

[Store300]

You can try triggering the following command (either on a button or using something like the AfterUpdate event):

Range("MyRange&quot.SpecialCells(xlCellTypeBlanks).Select

 

[bnageshrao]

Do I have to write a procedure and include your statement there and if so what kind of procedure, if you can elaborate it litttle bit more I will be thankful.

 

[bnageshrao]

Thanks a lot, it worked

 

[Store300]

A pleasure, glad it was useful.

 

[bnageshrao]

I got the cells highlighted, i want to know whether they can remail highlighted until the user fills in all those blank cells. Right now if i fill in one blank cells the other highlighted cells loose their highlight.

 

[Store300]

Slightly more difficult...You are probably best using the WorkbookAfterUpdate event, the only drawback is that it runs every time the user exits a cell. Need a hand or are you OK?

 

[bnageshrao]

I think I need your hand badly
Thanks

 

[bnageshrao]

When I used

Range("MyRange&quot.SpecialCells(xlCellTypeBlanks).Select

it gave me all blank cells, but when there were no blank cells it gave an error telling that there were no cells. I was expecting that vba will skip that statement when there were no blank cells. Any remedy for this and how to change the colors of blankcells if found any. Thanks.

 

[LoNeRaVeR]

bnageshrao:

Your request is to check for empty cells as soon as the user finishes inputting a row and highlight the blank cells. Without knowing the range of your data, I suggest highlighting all blank cells in the data entry row within the current data range after any data is entered.

In order for this to work the code provided below must be copied into the Sheet Object Module. In the VBA Editor Project Explorer, double-click on the Sheet that has the name of the sheet where you will want the highlighting. This will open that Sheet's Module. In the following example you'd double-click on the Sheet1 (Sheet1):

Microsoft Excel Objects
Sheet1 (Sheet1)

Copy and paste the following code into that Module.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Target.EntireRow. _
SpecialCells(xlCellTypeConstants). _
Interior.ColorIndex = xlNone
Target.EntireRow. _
SpecialCells(xlCellTypeBlanks). _
Interior.Color = vbYellow
End Sub

As soon as you make a change in that worksheet the cells missing data in the data entry row within the data range will be highlighted in Yellow.

The above code may be modified from nine lines to a mere five by removing the end of line underscore characters and moving the next line up in it's place. Worksheet_Change must keep it's underscore.

Enjoy,

LoNeRaVeR