Solved

Copying Conditional Formatting in Excel 2010

Posted on 2014-03-03
5
579 Views
Last Modified: 2014-03-03
Supposedly, you can copy conditional formatting from row to row when using a formula as your conditional format, but I can't seem to get it to work without modifying my rules in every single row. I keep reading that you should be able to make some of your references relative and then copy down, but it's not working for me.  I have a sample attached.  

What I want to do is if the first cell in the row is blank, I want it to use a fill color to fill in Columns A through E.  If the first cell is NOT blank, then I want it to use a different fill color in Columns A through E.  So I want to set this formula in Row 1, and then copy it down several rows without having to modify the rules in each row.  But whenever I modify my conditional formatting to be relative for either the formula or the "Applies to" part, then the conditional formatting doesn't work right.  

Please let me know what I'm doing wrong.  Thanks.
CONDITIONAL-FORMATTING-ISSUE.xlsx
0
Comment
Question by:esu4236
  • 4
5 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39900569
Change the conditional formats to:

=$A1=""

and

=$A1<>""

(notice: I removed $ from before the 1)

and change the Applies to ranges to cover whole range of interest...

e.g.

=$A$1:$E$3

or =$A:$E  (if you want entire columns)
0
 

Author Comment

by:esu4236
ID: 39900865
Okay I did that, but now how do I copy this conditional formatting to my other rows?  Because when I copied the conditional formatting using the Format Painter, it changed the "Applies to" to the cells that I had selected to copy into.  So my "Applies to" changed from $A$1:$E$3 to $A$4:$A$17. It did not change relative to the row ($A$4:$E$4 and so on). I tried using Paste Special, Formatting, but that didn't work either.

Getting closer.
0
 

Author Comment

by:esu4236
ID: 39900873
I uploaded the edited file so you can see what it did when I copied the conditional formatting down.
CONDITIONAL-FORMATTING-ISSUE.xlsx
0
 

Author Comment

by:esu4236
ID: 39900879
Oh, I think I got it.  I had to highlight the cells in columns B through E when copying the conditional formatting.  I think it's working right now.  I'm going to test a little more just to make sure.
0
 

Author Closing Comment

by:esu4236
ID: 39901206
Thanks for your help and such quick response.  It's working like a charm!!  Thanks again!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

911 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

16 Experts available now in Live!

Get 1:1 Help Now