Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copyConditional Formats
I have a macro/code which will apply a different Conditional Format to
each of four specific consequitive cells. (R2:U2) I also have this macro set up simple "Pick from the list" Data Validation in those same cells. Now I want to copy those four conditional formats and Data Validation settings all the way down until the end of the data. There is no data in any of these coloumns, but there is data one coloum to the left. The number of rows will vary considerably each time this macro is run. If I were to do this by keyoard I would copy R2:U2, go to Q2, [end][down][right], then [shifton][right][right][right][end][up][down][enter] I am sure that I can work this movement out in a macro with out too much problem. My biggest problem would be copying and pasting just conditional formats and Data Validations settings. Unless there is an all around easier way to do this? This spreadsheet, (all 24 of them) will be coming from Access. Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copyConditional Formats
Dim LastRow as long
with worksheets("somesheetname") lastrow = .cells(.rows.count,"Q").end(xlup).row 'apply your CF these: .range("r2:r" & lastrow)... .range("s2:s" & lastrow)... .range("t2:t" & lastrow)... .range("u2:u" & lastrow)... Phil Smith wrote: I have a macro/code which will apply a different Conditional Format to each of four specific consequitive cells. (R2:U2) I also have this macro set up simple "Pick from the list" Data Validation in those same cells. Now I want to copy those four conditional formats and Data Validation settings all the way down until the end of the data. There is no data in any of these coloumns, but there is data one coloum to the left. The number of rows will vary considerably each time this macro is run. If I were to do this by keyoard I would copy R2:U2, go to Q2, [end][down][right], then [shifton][right][right][right][end][up][down][enter] I am sure that I can work this movement out in a macro with out too much problem. My biggest problem would be copying and pasting just conditional formats and Data Validations settings. Unless there is an all around easier way to do this? This spreadsheet, (all 24 of them) will be coming from Access. Phil -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copyConditional Formats
I think I get what to do here.
Question: When I copy CF around, it will automatically adjust the formulas depending on my adressing. I assume that I create my target formula for the first cell in the range, and it will also automatically adjust as it goes down the range? Also "with" requires an "end with," right? Dave Peterson wrote: Dim LastRow as long with worksheets("somesheetname") lastrow = .cells(.rows.count,"Q").end(xlup).row 'apply your CF these: .range("r2:r" & lastrow)... .range("s2:s" & lastrow)... .range("t2:t" & lastrow)... .range("u2:u" & lastrow)... Phil Smith wrote: I have a macro/code which will apply a different Conditional Format to each of four specific consequitive cells. (R2:U2) I also have this macro set up simple "Pick from the list" Data Validation in those same cells. Now I want to copy those four conditional formats and Data Validation settings all the way down until the end of the data. There is no data in any of these coloumns, but there is data one coloum to the left. The number of rows will vary considerably each time this macro is run. If I were to do this by keyoard I would copy R2:U2, go to Q2, [end][down][right], then [shifton][right][right][right][end][up][down][enter] I am sure that I can work this movement out in a macro with out too much problem. My biggest problem would be copying and pasting just conditional formats and Data Validations settings. Unless there is an all around easier way to do this? This spreadsheet, (all 24 of them) will be coming from Access. Phil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to copyConditional Formats
I would expect that to happen--just like excel adjusts the formulas in cells.
But conditional formatting is strange. See John Walkenbach's site for an example: http://spreadsheetpage.com/index.php..._form atting/ And yep, you'll need to add an "end with" (along with lots more code <vbg) to make that suggestion close to useful. Phil Smith wrote: I think I get what to do here. Question: When I copy CF around, it will automatically adjust the formulas depending on my adressing. I assume that I create my target formula for the first cell in the range, and it will also automatically adjust as it goes down the range? Also "with" requires an "end with," right? Dave Peterson wrote: Dim LastRow as long with worksheets("somesheetname") lastrow = .cells(.rows.count,"Q").end(xlup).row 'apply your CF these: .range("r2:r" & lastrow)... .range("s2:s" & lastrow)... .range("t2:t" & lastrow)... .range("u2:u" & lastrow)... Phil Smith wrote: I have a macro/code which will apply a different Conditional Format to each of four specific consequitive cells. (R2:U2) I also have this macro set up simple "Pick from the list" Data Validation in those same cells. Now I want to copy those four conditional formats and Data Validation settings all the way down until the end of the data. There is no data in any of these coloumns, but there is data one coloum to the left. The number of rows will vary considerably each time this macro is run. If I were to do this by keyoard I would copy R2:U2, go to Q2, [end][down][right], then [shifton][right][right][right][end][up][down][enter] I am sure that I can work this movement out in a macro with out too much problem. My biggest problem would be copying and pasting just conditional formats and Data Validations settings. Unless there is an all around easier way to do this? This spreadsheet, (all 24 of them) will be coming from Access. Phil -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formats, how to scroll and view all formats? | Excel Worksheet Functions | |||
Copying formats - column widths, formats, outlining to worksheets | Excel Worksheet Functions | |||
Conditional formats- paste special formats? | Excel Discussion (Misc queries) | |||
paste conditional formats as formats | Excel Discussion (Misc queries) | |||
Formats: Too many different cell formats error message | Excel Programming |