Trying to make a check for users to indicate which cells still require input,
when they think they are done.
Rows not required at this point will be hidden already.
The rows to be checked are then obviously NOT hidden.
The cells that require input are unlocked (alway yellow) and "empty"
I'd like these cells to be somehow "changed" ie: pattern - assuming that the
following, which is part of my printing code, will not remove the pattern.
Cells. Select
Selection.Interior.ColorIndex = -4142.
Then, the code should step thru all the "empty" (blank?) cells, starting in
C6, ending at bottom right (E?), allowing them the possibility of entering
data in each of the empty cells (hopefully). The entry of data in a cell
should automatically remove the pattern.
I hope this is understandable.
Input greatly appreciated, as always.

Sub MissingInfo()

Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim LLastRow As Long
Dim c As Range

' Determine last row, using column B

LLastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow
With ws.Range("C6:E" & LLastRow)

' Put a pattern in the unlocked empty cells in columns C6, thru E ?.
' (Could be, in 1 row, Columns C, and/or D, and/or E or any combination
thereof)
' Then, starting at C6, go to first empty, unlocked cell and stop.
' User can input data ( or not ). When Enter selected, steps to next empty,
unlocked cell. etc..............
' Input of data will remove pattern from cell.
' Step thru should be from Top, and alway Left to Right, in each
applicable row

Excel doesn't work that way. It won't pause to allow the user to enter data.
You can prompt with an inputbox.

--
Regards,
Tom Ogilvy


"BEEJAY" wrote:

Trying to make a check for users to indicate which cells still require input,
when they think they are done.
Rows not required at this point will be hidden already.
The rows to be checked are then obviously NOT hidden.
The cells that require input are unlocked (alway yellow) and "empty"
I'd like these cells to be somehow "changed" ie: pattern - assuming that the
following, which is part of my printing code, will not remove the pattern.
Cells. Select
Selection.Interior.ColorIndex = -4142.
Then, the code should step thru all the "empty" (blank?) cells, starting in
C6, ending at bottom right (E?), allowing them the possibility of entering
data in each of the empty cells (hopefully). The entry of data in a cell
should automatically remove the pattern.
I hope this is understandable.
Input greatly appreciated, as always.

Sub MissingInfo()

Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim LLastRow As Long
Dim c As Range

' Determine last row, using column B

LLastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow
With ws.Range("C6:E" & LLastRow)

' Put a pattern in the unlocked empty cells in columns C6, thru E ?.
' (Could be, in 1 row, Columns C, and/or D, and/or E or any combination
thereof)
' Then, starting at C6, go to first empty, unlocked cell and stop.
' User can input data ( or not ). When Enter selected, steps to next empty,
unlocked cell. etc..............
' Input of data will remove pattern from cell.
' Step thru should be from Top, and alway Left to Right, in each
applicable row

Tom, Thank-you
If you can help resolve my reply on my other post, I can likely use a
modification of it to accomplish a least part of what I was looking for here.
It is likely even better, because it will make the salesmen think and do
more, so hopefully, they will fill things out completely the first time round.

Thanks again.
JFS

"Tom Ogilvy" wrote:

Excel doesn't work that way. It won't pause to allow the user to enter data.
You can prompt with an inputbox.

--
Regards,
Tom Ogilvy


"BEEJAY" wrote:

Trying to make a check for users to indicate which cells still require input,
when they think they are done.
Rows not required at this point will be hidden already.
The rows to be checked are then obviously NOT hidden.
The cells that require input are unlocked (alway yellow) and "empty"
I'd like these cells to be somehow "changed" ie: pattern - assuming that the
following, which is part of my printing code, will not remove the pattern.
Cells. Select
Selection.Interior.ColorIndex = -4142.
Then, the code should step thru all the "empty" (blank?) cells, starting in
C6, ending at bottom right (E?), allowing them the possibility of entering
data in each of the empty cells (hopefully). The entry of data in a cell
should automatically remove the pattern.
I hope this is understandable.
Input greatly appreciated, as always.

Sub MissingInfo()

Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim LLastRow As Long
Dim c As Range

' Determine last row, using column B

LLastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow
With ws.Range("C6:E" & LLastRow)

' Put a pattern in the unlocked empty cells in columns C6, thru E ?.
' (Could be, in 1 row, Columns C, and/or D, and/or E or any combination
thereof)
' Then, starting at C6, go to first empty, unlocked cell and stop.
' User can input data ( or not ). When Enter selected, steps to next empty,
unlocked cell. etc..............
' Input of data will remove pattern from cell.
' Step thru should be from Top, and alway Left to Right, in each
applicable row

You could try a non-macro solution and use conditional formatting. If
the cell value is ="" then have a color, and it will change when there
is text there (alternatively, you could have cell value is note equal
to ="" and then the formatting will also change when text is entered).
As Tom Ogilvy pointed out, the only way to enter data while a macro is
running is through an input box
The only way to do something similar to what you're asking would be to
put some macros in the workbook that capture the Workbook_SheetChange
event. The routine could change the format of the changed cell(s).
The SheetChange event will pass the changed range and and the
worksheets that were changed. Because users could skip cells and
leave them intentionally blank, you won't want to revert to the last
blank cell every time. You could have the macro select the next cell
after the last cell changed.
FYI, you should be careful about using the
specialcells(xlcelltypelastcell) - I'm pretty sure that it doesn't
always return the last cell in a workbook (If cells have been deleted
or changed since the last save).
I realize that my suggestion has been rather vague, but I think you
could try and use the change events to move the focus. If you do
decide to take this route and have any questions, please let me know.
I hope this helps.

On Mar 13, 1:44 pm, BEEJAY wrote:
Trying to make a check for users to indicate which cells still require input,
when they think they are done.
Rows not required at this point will be hidden already.
The rows to be checked are then obviously NOT hidden.
The cells that require input are unlocked (alway yellow) and "empty"
I'd like these cells to be somehow "changed" ie: pattern - assuming that the
following, which is part of my printing code, will not remove the pattern.
Cells. Select
Selection.Interior.ColorIndex = -4142.
Then, the code should step thru all the "empty" (blank?) cells, starting in
C6, ending at bottom right (E?), allowing them the possibility of entering
data in each of the empty cells (hopefully). The entry of data in a cell
should automatically remove the pattern.
I hope this is understandable.
Input greatly appreciated, as always.

Sub MissingInfo()

Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim LLastRow As Long
Dim c As Range

' Determine last row, using column B

LLastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow
With ws.Range("C6:E" & LLastRow)

' Put a pattern in the unlocked empty cells in columns C6, thru E ?.
' (Could be, in 1 row, Columns C, and/or D, and/or E or any combination
thereof)
' Then, starting at C6, go to first empty, unlocked cell and stop.
' User can input data ( or not ). When Enter selected, steps to next empty,
unlocked cell. etc..............
' Input of data will remove pattern from cell.
' Step thru should be from Top, and alway Left to Right, in each
applicable row

Meatshield:
Thanks for your input.
It looks like have another way to tackle this, that I like even better than
what I was first trying to do.
Thanks again.


"meatshield" wrote:

You could try a non-macro solution and use conditional formatting. If
the cell value is ="" then have a color, and it will change when there
is text there (alternatively, you could have cell value is note equal
to ="" and then the formatting will also change when text is entered).
As Tom Ogilvy pointed out, the only way to enter data while a macro is
running is through an input box
The only way to do something similar to what you're asking would be to
put some macros in the workbook that capture the Workbook_SheetChange
event. The routine could change the format of the changed cell(s).
The SheetChange event will pass the changed range and and the
worksheets that were changed. Because users could skip cells and
leave them intentionally blank, you won't want to revert to the last
blank cell every time. You could have the macro select the next cell
after the last cell changed.
FYI, you should be careful about using the
specialcells(xlcelltypelastcell) - I'm pretty sure that it doesn't
always return the last cell in a workbook (If cells have been deleted
or changed since the last save).
I realize that my suggestion has been rather vague, but I think you
could try and use the change events to move the focus. If you do
decide to take this route and have any questions, please let me know.
I hope this helps.

On Mar 13, 1:44 pm, BEEJAY wrote:
Trying to make a check for users to indicate which cells still require input,
when they think they are done.
Rows not required at this point will be hidden already.
The rows to be checked are then obviously NOT hidden.
The cells that require input are unlocked (alway yellow) and "empty"
I'd like these cells to be somehow "changed" ie: pattern - assuming that the
following, which is part of my printing code, will not remove the pattern.
Cells. Select
Selection.Interior.ColorIndex = -4142.
Then, the code should step thru all the "empty" (blank?) cells, starting in
C6, ending at bottom right (E?), allowing them the possibility of entering
data in each of the empty cells (hopefully). The entry of data in a cell
should automatically remove the pattern.
I hope this is understandable.
Input greatly appreciated, as always.

Sub MissingInfo()

Dim ws As Worksheet
Set ws = Worksheets("Pricing")
Dim LLastRow As Long
Dim c As Range

' Determine last row, using column B

LLastRow = ws.Range("B:B").SpecialCells(xlCellTypeLastCell).R ow
With ws.Range("C6:E" & LLastRow)

' Put a pattern in the unlocked empty cells in columns C6, thru E ?.
' (Could be, in 1 row, Columns C, and/or D, and/or E or any combination
thereof)
' Then, starting at C6, go to first empty, unlocked cell and stop.
' User can input data ( or not ). When Enter selected, steps to next empty,
unlocked cell. etc..............
' Input of data will remove pattern from cell.
' Step thru should be from Top, and alway Left to Right, in each
applicable row