How to copyConditional Formats

  • Thread starter Thread starter Phil Smith
  • Start date Start date
P

Phil Smith

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]
, then
[shifton]
[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​
 
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 said:
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]
, then
[shifton]
[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​
Click to expand...
​
 
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 said:
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 said:
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]
, then
[shifton]
[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​
Click to expand...

​
Click to expand...
​
 
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.ph...formula1_property_for_conditional_formatting/

And yep, you'll need to add an "end with" (along with lots more code <vbg>) to
make that suggestion close to useful.



Phil said:
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 said:
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 said:
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]
, then
[shifton]
[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​
Click to expand...

​
Click to expand...
​
Click to expand...
​
 
Back
Top