• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

Excel VBA - copying formats

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
hindersaliva
Asked:
hindersaliva
  • 3
  • 2
  • 2
3 Solutions
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
hindersalivaAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
hindersalivaAuthor Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> "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
 
Roy CoxGroup Finance ManagerCommented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now