You are here:

Excel/Auto Fill according to some rules

Advertisement


Question
Exemple of necessity
Exemple of necessity  
QUESTION: Hello, I need help creating a macro.

I need a macro that when I put a value "Y" in a cell "X" this value causes other cells to auto fill with # until you reach the cell corresponding to the value "Y" in the same line and also this final cell is painted green.

I'll explain a bit the real situation for greater understanding.

Periodically I have to check if some documents have suffered updates, after checking I need to populate a cell with the current revision of the document and then fill some cells with X until you reach the actual review.

For example, if the document is in revision A D, so I'll fill 5 cells that are in sequence 0, A, B, C and D.
Then the cell corresponding to the revision 0 will be filled with X, the cell referring to the revision A will be filled with X and so will everyone else until get to cell for revision D, and this last will be painted green.

I am attaching a picture of the spreadsheet to aid understanding.

I hope you understand and can help me.

I am grateful already, thanks.

ANSWER: Thiago,


Right click on the sheet tab of the worksheet that you want this behavior.  Select view code.

Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, r As Range
If Target.Column = 6 Then   ' change in column F
 If Target.Row > 4 Then   ' change is beyond row 4
     Set r = Range("G4:R4")
     If Application.CountIf(r, Target.Value) > 0 Then
     For Each cell In r
        Cells(Target.Row, cell.Column).Interior.ColorIndex = xlNone
        Cells(Target.Row, cell.Column).Value = "x"
        If cell.Value = Target.Value Then
         Cells(Target.Row, cell.Column).Interior.ColorIndex = 14
         Exit For
        End If
     Next
     End If
  End If
End If

End Sub


Do this on a copy of your workbook until you are sure this does what you want.

Now make a change in column F and it should put in the x's in that row.  Macros must be enabled.  (events must be enabled but they should be enabled unless you disable them).

Lightly tested but worked as I expected/describe.
--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom, it work very well but i have a problem.
In this way i need to insert the values manually. I can't do it by copy and past values because the macro breaks only works if i copy one value and paste one for one, i want something more robotic, more automatic.
Because i need to copy and past multiple values.
Can you help me on it?

ANSWER: Thiago,

This should do what you describe.

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
Dim cell As Range, r As Range
Dim cellrr As Range, rr As Range
If Not Intersect(Target, Columns(6)) Is Nothing Then
 Set rr = Intersect(Target, Columns(6))
 Set r = Range("G4:R4")
 For Each cellrr In rr   ' change in column F
  If cellrr.Row > 4 Then   ' change is beyond row 4
     
    If Application.CountIf(r, cellrr.Value) > 0 Then
        With cellrr.Offset(0, 1).Resize(1, r.Columns.Count)
         .Interior.ColorIndex = xlNone
         .ClearContents
        End With
     For Each cell In r
        Cells(cellrr.Row, cell.Column).Value = "x"
        If cell.Value = cellrr.Value Then
         Cells(cellrr.Row, cell.Column).Interior.ColorIndex = 14
         Exit For
        End If
     Next
    End If
  End If
 Next
End If

End Sub

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Tom, thank you very much is working perfectly.

If I may be a little lazy, could make some scheme so that when after some value to be inserted in the "F" column even if you deleted the "X" to continue in the other columns would vanish if and only me to select them manually and erased or fell within a new value in the "F" column.

I do not know if I could be sure, but the goal is that the "F" column would only be a basis for completing the "X" in the other columns then put this data in the "F" column need to be deleted.

Could you do this?

Answer
Thiago,

Not sure what you are asking.   Are  you saying if you go to a row and put in an x say column J, then the macro should fill that row from G:J with an x and color the cell in column J for that row as green and put a J in column F for that row?  Is that what you are describing?

If that isn't what you are saying, then I don't understand what you are saying you want the macro to do.  

--
Regards,
Tmo Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.