You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Auto Fill according to some rules

Advertisement

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?

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

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | No Comment |

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

Answers by Expert:

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.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.