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
Solved

Excel VBA - copying formats

Posted on 2016-11-16
7
52 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 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 100 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 18

Assisted Solution

by:Roy_Cox
Roy_Cox earned 400 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 19
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 19
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 18

Accepted Solution

by:
Roy_Cox earned 400 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

856 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