Solved

Excel VBA - copying formats

Posted on 2016-11-16
7
17 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 18

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 100 total points
Comment Utility
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
Comment Utility
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 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 400 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18
Comment Utility
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
Comment Utility
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 18
Comment Utility
> "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 17

Accepted Solution

by:
Roy_Cox earned 400 total points
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now