Solved

Excel VBA - copying formats

Posted on 2016-11-16
7
58 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 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 20

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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 20

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

726 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