conditional formatting and macro??

[tanky]

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

 

[JoeMo]

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]

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]

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]

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]

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-comfficeffice" /><o></o>
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></o>
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></o>
Is this possible to do in excel? Any information will be grateful <o></o>
Many thanks T<o></o>

 

[Smitty]

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]

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]

This should be all you need:

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

 

[tanky]

It works!

Great, thank you very much Smitty!!