duplicate a macro button for every row

**artem1s** · 06-21-2015, 03:57 PM

Hi there,

Very very new to excel/macros etc.
working in excel 2010.
I have created a macro button that changes the value of one cell, to the value of another cell.

E.g. when the button is pressed, the value in B2 changes to whatever value is in B1

here -

Sub Stock_up()
Range("F2").Value = Range("E2").Value
End Sub

i'd like to somehow just 'drag down' the buttons, like can be done with formula in excel cells. it is a basic stock control spreadsheet i am creating.

Also, as a bonus extra, i'd like to create some kind of conditional formatting, where upon if ANY of values in column B show a specific value (0), then a separate cell shows up red, or whatever.
e.g. b1, b2 and b3 all have values of 3, but b4 has a value of 0. Or, b1 and b3 have values of 3, but b2 and b4 have values of 0. i'd like cell D5 to fill red upon this 0 value, in any cell.

Thank you kindly

Katie x

**AlphaFrog** · 06-21-2015, 05:03 PM

Hi and welcome to the forum.

There are two types of buttons you can put on a worksheet; ActiveX-type and Form-Type
Overview of forms, Form controls, and ActiveX controls on a worksheet

For future reference, please state what type of worksheet controls you are using.

The method below uses Form-type buttons because you can assign one macro to all the buttons. The macro figures out the row number of the button that called it.

Put a Form-type button on your sheet and assign this macro to it.
Then copy the cell that contains the button and paste it in as many rows as you like.

Please Login or Register  to view this content.

Also, as a bonus extra, i'd like to create some kind of conditional formatting, where upon if ANY of values in column B show a specific value (0), then a separate cell shows up red, or whatever.
e.g. b1, b2 and b3 all have values of 3, but b4 has a value of 0. Or, b1 and b3 have values of 3, but b2 and b4 have values of 0. i'd like cell D5 to fill red upon this 0 value, in any cell.

Use this as the CF formula in cell D5
=COUNTIF(B1:B4,0)>0