I need to suggest a value

[Guest]

Hello:
Please, I would appreciate help with this problem. I want Excel to suggest
the value of the previous row, each time the current cell is empty, but with
no need to define this with the classic native method (select, drag....).
Something like conditional format that says:
each time cell (A x) is empty --> suggest value of cell (A x-1)

If A6 is empty, Excel should suggest A5 value

I need this behaviour only for B column.

Thanks very much for your attention. My best regards.
Roberto

 

[Rick Rothstein \(MVP - VB\)]

I'm not entirely sure what you mean by "suggest". The following code...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Application.Selection.Count = 1 Then
If Target.Row > 1 And Target = "" Then
Target.Value = Target.Offset(-1, 0)
End If
End If
End If
End Sub

will place the text from the cell above in the current Column B cell and
leave focus on cell so you can just type in a new value, but there will be
no visible indication (other then the selection indicator) to let you know
the cell contents are copied. If this were a TextBox, I would select all the
text in the TextBox so you would know the entry is pending "approval", but I
don't know how to do this in a cell... and even if I did know how, it would
probably be dependent on the

Tools/Options/Edit/Edit directly in cell

option being checked.

Target.Row > 1 And Target = ""

Rick

 

[Guest]

Thanks Rick.!!

This Worksheet_SelectionChange will work like a standard Visual Basic event ?
Can you give me a link to see all similar events listed ?

 

[Rick Rothstein \(MVP - VB\)]

This Worksheet_SelectionChange will work like a standard

Visual Basic event? Can you give me a link to see all similar
events listed ?

SelectionChange is a standard event for the Worksheet object. You can see
all of the Worksheet events by going into the VBA editor, clicking on any
Sheet object shown in the Project Explorer window, then selecting Worksheet
from the combo box on the left side of the Code window and, finally, looking
at all the available events in the combo box on the right side of the Code
window.

Rick