Solved

Excel automatic formatting

Posted on 2014-11-25
4
148 Views
Last Modified: 2014-11-26
Hi all!

I've prepared a formula that colors my cell following various rules

Everything works great but if I copy the formula for all the column it won't work for all the rows...

I need this formula to work on both Excel and Libre Office Calc.

What am I doing wrong?

See example attached.
test.xlsx
0
Comment
Question by:ltpitt
  • 2
4 Comments
 
LVL 15

Expert Comment

by:WalkaboutTigger
ID: 40465953
I opened your attached file and saw no conditional formatting and no VBA (opened Visual Basic Editor from the Developer tab.

Please save your working example as a Macro-enabled Excel workbook (XLSM extension).
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40466012
You only need to change the relative/absolute cell referencing in two of your conditional format formulas.

First, open the Conditional Formatting Rules Manager (Menu: Home, Styles group, Conditional Formatting, Manage Rules...)
Change the top filter to "This Worksheet"
CFRMDouble-click the first rule and change the greater than formula to =$H2 and click OK.
CF rule 1Double-click the second rule and change the last formula to =$H2 and click OK.
CF rule 2Your final rule can be left as-is.
Click the "OK" button to close.

You can now copy the cell down as many rows as needed and the conditional formatting rule will follow.

Regards,
-Glenn
0
 
LVL 1

Author Closing Comment

by:ltpitt
ID: 40466304
That's what a perfect solution should be!

Thanks :D
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40467029
You're welcome.  Happy Thanksgiving!
-Glenn
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 …
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 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

23 Experts available now in Live!

Get 1:1 Help Now