VBA code to auto fill cells

[sangeeta25]

Hi i have a drop down list in column c and what i would like is, in any cell of column c, if from the drop down list, the option 'abandoned' is celected, then i would like for that row, any blank cells, to automatically be filled with the term 'abandoned' as well.

For example, if a user selects 'abandoned' from the drop down list in cell, lets say, c6, then whatever cells a blank in row 6, then those cells will be automatically filled with the term abandoned.

Does anyone know how to construct a code for this to put in the visual basic section of excel????

So below shows an example, say column C, someone has selected from the drop down list, 'abandoned' then i would like for the blank cells of that row, i.e. cells A2, B2 and E2, to automatically, once abandoned is selected from column C, to be filled with the word abanoned as well.

number	date	manager	list	signed
		Abandoned	yes

<tbody>
</tbody>

 

[Joe4]

You should be able to do this with a Worksheet_Change event procedure, which we can run any time column C is updated.

Right-click on the sheet tab name at the bottom of your sheet, select View Code, and copy and paste this VBA code in the VB Editor Window. It should do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myRow As Long
    Dim myCol As Long

'   Only run if column C is upated
    If Target.Column = 3 Then
'       Loop through all values in column C that were updated
        For Each cell In Target
'           Check to see if column C = "Abandoned"
            If cell = "Abandoned" Then
                myRow = cell.Row
'               Loop through all columns in row (up to 5), and update if necessary
                For myCol = 1 To 5
                    If myCol <> 3 And Cells(myRow, myCol) = "" Then Cells(myRow, myCol) = "Abandoned"
                Next myCol
            End If
        Next cell
    End If

End Sub

 

[sangeeta25]

Hi, thank you, this works but to an extent.
Is there any possibility that you can change this code so that when abandoned is selected in column c then the rest of the cells in that row fills abandoned too BUT only if those cells are blank?

 

[sangeeta25]

apologies, that's fine it works.

thanks very much

 

[Joe4]

Is there any possibility that you can change this code so that when abandoned is selected in column c then the rest of the cells in that row fills abandoned too BUT only if those cells are blank?

It does, but you might have to modify it slightly to work for you.

In your example, you had 5 columns.
Note this line of code:

'               Loop through all columns in row (up to 5), and update if necessary
                For myCol = 1 To 5

If you really have more than 5 columns, change the "5" to the real number of columns you have (and want populated).