?
Solved

Changing font color in Range if specific conditions are met.

Posted on 2003-03-29
9
Medium Priority
?
824 Views
Last Modified: 2013-12-03
Is there a way to change the font color in a specified Range if certain conditions are met.

I am working on a spreadsheet that I would like to have the text in columns "C", "E", "G", "I", and "K" be BLACK if the number in the cell is equal to or greater than 12, but would be RED if is less than 12.

Thanks for any help anyone might be able to send my way.
0
Comment
Question by:thirdwalker
[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
  • 5
  • 2
  • 2
9 Comments
 
LVL 45

Accepted Solution

by:
patrickab earned 200 total points
ID: 8231711
Use a macro as follows:

\a {if A10>=12}{SELECT C1..C8192;C1}{SELECT-APPEND E1..E8192;E1}{SELECT-APPEND G1..G8192;G1}{SELECT-APPEND I1..I8192;I1}{SELECT-APPEND K1..K8192;K1}{STYLE-INTERIOR ;;;129}
{if A10<12}{SELECT C1..C8192;C1}{SELECT-APPEND E1..E8192;E1}{SELECT-APPEND G1..G8192;G1}{SELECT-APPEND I1..I8192;I1}{SELECT-APPEND K1..K8192;K1}{STYLE-INTERIOR ;;;255}
{esc}

To use the macro use CTRL+A. If you haven't used macros before in Lotus123 please let me know and I will give you the help needed to make it work.

There are only three lines in the macro and the first two start with {if A10...etc}. A10 contains the number that is either larger or smaller than 12 and which determines the colour of the text in the columns you specified. The last line just has {esc} in it to release the highlighting of the columns. By the way if you Lotus123 has longer columns you will need to edit the macro so that the last line of the whole spreadsheet is specified instead of line 8192.

Hope that helps
0
 
LVL 45

Expert Comment

by:patrickab
ID: 8231838
By the way if you want to experiment with other colours you only need to change the number in {STYLE-INTERIOR ;;;255} to whatever colour number you want to use. You could of course put a cell reference instead -like {STYLE-INTERIOR ;;;A1} where A1 contains the colour reference for one colour and {STYLE-INTERIOR ;;;A2} where A2 contains the colour reference number for the other colour.

Hope that adds a bit of flexibility.
0
 

Author Comment

by:thirdwalker
ID: 8232254
Thank you very much..

Great help!
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 45

Expert Comment

by:patrickab
ID: 8233226
Pleasure - pleased to help. Thanks for the A grade. Regards, Patrick
0
 
LVL 1

Expert Comment

by:Paul_Lautman
ID: 8290219
Might I offer a slicker method.
Go to http://www.geocities.com/lee_m2/addins.html
and download the SETSTYLE addin.
This allows cell attributes to change dynamically (i.e. no need to run a macro)  based on cell values.
Gives 123 similar function to Excel's conditional formatting.
0
 

Author Comment

by:thirdwalker
ID: 8296918
Thank you VERY much.  Very easy, and works like a charm.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 8398084
Paul_Lautman - I had a look at the addin. It does work but it is messily written, slows down larger spreadsheets, and lacks versatility. Still it is more dynamic than a macro but does need extra parallel cells to make it work whereas a macro does not. Mixed blessings I'd say.
0
 
LVL 1

Expert Comment

by:Paul_Lautman
ID: 8398798
I have always been impressed by Lee's work. If you think it messy, I'm sure he'd be glad of your expertise in helping him to become a competent programmer.
I think you'll find that adding any function to a spreadsheet slows it down. The larger the spreadsheet the slower it is.
Can you explain how it lacks versatility. As far as I can see it enables one to change any style element for an almost infinite set of criteria. Just what other versatility were you looking for?
Also you say "it is more dynamic than a macro". I was under the impression that a macro had to be run and therefore was not dynamic at all. Surely something is either dynamic or not in the same way that someone is dead or not. Or do you believe that there are degrees of death/dynamics?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 8400482
1. It's messy
To have an inconsistent syntax means that it is not easy to use without referring to the notes. Even the ranges are defined differently according to the use. They are also not Lotus123-like. Using for example the ! symbol is very definately non-Lotus123.

2. Slows it down
Sure you're correct the more that's added the slower any spreadsheet works. In this instance there is a noticeable slowing down - admittedly our pc's at work are only 750MHz m/c's.

3. Lacks versatility
For example it appears to only accept some basic colour names like "red" when the colour gamut could be 255 colours - it's possible that other colour names can be used but as they are not listed I don't know.

4. Dynamic
I've already said it is dynamic and that is an advantage over a macro. This part of my comment has been lost by your unnecessary sarcasm.

5. Help file
This should be available on-line through the Lotus123 interface - not just a read alone text file.

6. Programming help
As a professional programmer I would guess you are able to help far better than me. However that does not stop me from commenting on this or any other piece of software that I use.

Hope this gives an adequate response to your queries.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month10 days, 2 hours left to enroll

762 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