Excel/Auto fill with some rules
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: If I've understood correctly, http://www.aidanheritage.byethost3.com/excel/ae_autofill_By_Macro.xlsm
will give you what you want. I would suggest using conditional foratting to handle the colour.
An alternative would be to have formulas in place in all cells - example here from B2
By typing an X in any one cell all the ones to left would then fill via the formula.
---------- FOLLOW-UP ----------
QUESTION: Hi Aidan,
That's a very good way but i will still have to fill the cell manually.
I want something more robotic, automatic.
i will try to give a better example.
I fill the cell F5 with "C".
So the value C in this cell means that the cells G5, H5, I5 and J5 will be filled with "X".
Until there i've done, colecting peaces of macros that i found.
My problem is when i erase the column F5 and i lost my data, all the cells G5, H5, I5 and J5 be empty.
I want to write the value in the cell F5 and independent if will erase this cell the cells G5, H5, I5 and J5 will keep filled therefore i fill the cell F5 with another data like B or E.
i will attach an image for you to see.
I can see that I misunderstood from the first question - I'll try again. This macro will do what you want I think
Private Sub Worksheet_Change(ByVal Target As Range)
Dim colvar As Long, rowvar As Long, looper
On Error GoTo exit_sub
If Target.Cells.Count = 1 Then
If Target.Column = 6 Then
'need to match the value
looper = WorksheetFunction.Match(Target.Value, Range("G2:BD2")) + 6
'colvar now contains the matching row
rowvar = Target.Row
For colvar = 7 To looper
Cells(rowvar, colvar).Value = "X"
With Cells(rowvar, looper).Interior
.Color = 5287936
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.TintAndShade = 0
.PatternTintAndShade = 0
'conditional formatting would work better - shade IF cell has a value and cell to the right has NO value
If it helps for any follow ups my direct email is email@example.com