conditional formatting and macro??

tanky

New Member
Joined
May 22, 2010
Messages
14
Hi

I've currently creating a spreadsheet where some cells have conditional formatting. As this spreadsheet needs to constantly updated by various people, does anyone know if there is a way of creating additional rows without having to copy and paste the conditional formats from the previous row? e.g. a simple button at the end of the row? Do I need to use macro?

The spreadsheet will be shared with others and they're not familiar with conditional formatting, they may not know that they need to copy and paste the format if they were to add rows - so if there was a way to simply click a button at the end of the row to add a blank row below which will follow the same conditional format as the row above, that would be helpful?

Any advice would be grateful
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,235
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi

I've currently creating a spreadsheet where some cells have conditional formatting. As this spreadsheet needs to constantly updated by various people, does anyone know if there is a way of creating additional rows without having to copy and paste the conditional formats from the previous row? e.g. a simple button at the end of the row? Do I need to use macro?

The spreadsheet will be shared with others and they're not familiar with conditional formatting, they may not know that they need to copy and paste the format if they were to add rows - so if there was a way to simply click a button at the end of the row to add a blank row below which will follow the same conditional format as the row above, that would be helpful?

Any advice would be grateful
If you create the conditional format for the entire column it will carry to any rows that are added to the column.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

This should do what you want (copy the active cell row's formatting to the one below it and select that row):

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> ApplyCF()<br>    <SPAN style="color:#00007F">With</SPAN> ActiveCell<br>        .EntireRow.Copy<br>        <SPAN style="color:#00007F">With</SPAN> .Offset(1)<br>            .PasteSpecial xlPasteFormats<br>            .Select<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,
 

tanky

New Member
Joined
May 22, 2010
Messages
14
Hi Smitty

Thank you very much for this, I tried to use the codes you gave me but it seemed to highlight copy the row but not paste?

I'm trying to make the spreadsheet as simple as possible. I'd like the spreadsheet to to contain some data which will remain constant throughout e.g. country, city, reference code etc., and there are only few data such as personnel, date and budget which will require manual data entry changed. So I was thinking of creating an "add row" button at the top of the spreadsheet which once clicked, will automatically create a new row with the necessary information except for the data field which require manual change? Is this do-able in excel?

Any info would be grateful
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,803
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hello Tanky

Smittys code only pasted the formats. You said in your original questions that you need the CF to apply but that users are not familiar with it. Pasting only formats will carry CF with it.

Can you elaborate on your latest requirement please? Which columns remain constant and what values should those hold?
 

tanky

New Member
Joined
May 22, 2010
Messages
14
Hi Jon
Thanks for your quick response.
This is an example of what I’m trying to achieve (Excel 2007):
So from cells A2 through to F2, we have data which doesn’t require changes: such as country, city, reference code, budget code etc. Some cells such as A2 will have drop down menu with countries listed via Data Validation. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I have G2 through to J2 which have conditional formats and they have been set up to flag up dates that are out of window or where the dates they insert are close to deadline. <o:p></o:p>
As this file will be shared among a number of users, I would like to create an “add row” button under say K column, so that this would create another row where they can pick the country from the drop down menu in the A column and enter the date which has CF in columns G to J.<o:p></o:p>
Is this possible to do in excel? Any information will be grateful <o:p></o:p>
Many thanks T<o:p></o:p>
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536

ADVERTISEMENT

Try amending the code just a bit:

ActiveCell.EntireRow.Copy ActiveCell.Offset(1)

From there you can clear the cells that should get data (e.g. Cells(ActiveCell.Offset(1).Row,"A").ClearContents).
 

tanky

New Member
Joined
May 22, 2010
Messages
14
Thank you so much Smitty but I think I'm entering it wrong?

Sub Button11_Click()
With ActiveCell.EntireRow.Copy ActiveCell.Offset(1)
.EntireRow.Copy
With .Offset(1)
.PasteSpecial xlPasteFormats
.Select
End With
Application.CutCopyMode = False
End With
End Sub

T
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
This should be all you need:

Code:
Sub Button11_Click()
  ActiveCell.EntireRow.Copy ActiveCell.Offset(1)
  Application.CutCopyMode = False
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,451
Messages
5,528,836
Members
409,839
Latest member
akashsadhu
Top