Solved

Excel automatic formatting

Posted on 2014-11-25
4
142 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
Comment Utility
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
Comment Utility
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
Comment Utility
That's what a perfect solution should be!

Thanks :D
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
You're welcome.  Happy Thanksgiving!
-Glenn
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

12 Experts available now in Live!

Get 1:1 Help Now