• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

Changing font color in Range if specific conditions are met.

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
thirdwalker
Asked:
thirdwalker
  • 5
  • 2
  • 2
1 Solution
 
patrickabCommented:
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
 
patrickabCommented:
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
 
thirdwalkerAuthor Commented:
Thank you very much..

Great help!
0
Technology Partners: 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!

 
patrickabCommented:
Pleasure - pleased to help. Thanks for the A grade. Regards, Patrick
0
 
Paul_LautmanCommented:
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
 
thirdwalkerAuthor Commented:
Thank you VERY much.  Very easy, and works like a charm.
0
 
patrickabCommented:
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
 
Paul_LautmanCommented:
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
 
patrickabCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now