?
Solved

Excel VBA - copying formats

Posted on 2016-11-16
7
Medium Priority
?
66 Views
Last Modified: 2016-12-01
I can copy entire cells like this - values, formats, everything.

Range("RowMask").Copy Destination:=rngDataRange

Open in new window


I want to copy only the Formats, Conditional Formats. How do I amend the above please?

Thanks

Edit: I want to avoid a Copy > Paste Formats that you get when you record a macro. The above is simpler and faster (?).
0
Comment
Question by:hindersaliva
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 21

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 400 total points
ID: 41890045
The macro recorder is good to see what commands you need -- but what it creates can be modified ;)  
    Range("B261").Copy 'choose cell with format you want
    Range("B263:B275").PasteSpecial Paste:=xlPasteFormats 'replace with range you want
    Application.CutCopyMode = False

Open in new window

0
 

Author Comment

by:hindersaliva
ID: 41890082
Crystal, I'd like to avoid Copy > Paste. See my sample code in the question. That way (I think) is much cleaner (but I could be wrong). How do I modify that particular syntax/coding style (above) to do only the Formats?

Of course, if someone says Copy > Paste is the only available way then that would help me  also.
0
 
LVL 21

Assisted Solution

by:Roy Cox
Roy Cox earned 1600 total points
ID: 41890101
That method will copy conditional formatting & values or formulas as below.

Range("A1").Copy Range("C1")

Open in new window


Is it not working for you?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 21
ID: 41890109
you can also use VBA to get specific formats and iterate through conditional formatting -- what exactly do you want to copy on the cell format?  You can use Styles for your cells.  I normally use them to detect what a cell is for processing changes but of course they can be used for their intended purpose too! Styles does not include conditional formatting -- but that can be iterated with VBA. Don't have time right now to write that code, perhaps someone else will jump in.

For Styles: select cell with the style you like
on Home, click drop-down in Styles and choose New Cell Style.
Excel Cell Style -- New Cell StyleA dialog box will pop up and you can give the Style a Name and choose what formats the style will apply.
Excel Cell Style -- choose name and formatWhen I use Styles, I normally customize my QAT (Quick Access Toolbar) to apply that style quickly.

> "avoid Copy > Paste"
of course -- the code I gave you will only paste format and will also get conditional formatting since it uses PasteSpecial Paste:=xlPasteFormats
0
 

Author Comment

by:hindersaliva
ID: 41890136
Roy, you're on the right lines.

The scenario is, I have cells that I make change to a Red fill color when updated (Worksheet_Change). Thus these cells are marked 'dirty' - and a 'Save' button lights up. When the users have finished updating cells he/she 'saves' the updates by clicking the (now highlighted) 'Save' button. The Save procedure updates the 'dirty' cell data to an Access database. All that works fine.

Clicking save should now change the 'dirty' cells back to their original Format. These include Conditional Formats on some column. The original formats are on a hidden area in a Range("RowMask") - just one row at the top of sheet.

So ...... I need to repaint the 'dirty' cells with the Formats, but not overwrite the values.

Coding Copy > PasteSpecial Formats will do the job, but I think that will be slower than Source Destination (which is cleaner I think). Hence the question.
0
 
LVL 21
ID: 41890145
> "The Save procedure updates the 'dirty' cell data to an Access database"

awesome! Access is a great place to save data! Code could be put into this procedure to change the format. If no one else jumps in before tonight, I can figure that out -- just can't do it right now.
0
 
LVL 21

Accepted Solution

by:
Roy Cox earned 1600 total points
ID: 41890902
The range copy method will copy everything. There is no method to do a similar method with formatting only.

Why not have another rule that works in the opposite way to the first?  I'm not sure what your rule is but if need be you could refer to a "key" cell that would be changed after the button is clicked

In the example I have used E1 to remove the format, so when your button is run it would enter "Complete" into E1 to remove the highlighting
CopyAll.xlsm
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question