×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Displaying cell values in different colours depending on value
5

Displaying cell values in different colours depending on value

Displaying cell values in different colours depending on value

(OP)
Hi, all.

Does anyone have a way of colour coding cell values (in Excel) conditionally?

(eg, if a stress value exceeds allowable, display it in red, if OK display it in green)

I would enthusiastically propose any simple working solution for 'Eng-Tip of the Year'

RE: Displaying cell values in different colours depending on value

4
Look under Format|Conditional Formatting.  I think you'll find everything you need there.  Post again if this is not sufficient information to get you going.

RE: Displaying cell values in different colours depending on value

(OP)
Hi, ivymike.

Brilliant (or was I the only user not to know about that trick?).  That IS enough to get me going.  

I am running version 7.0 of Excel, which is not very forthcoming about conditional formatting - no mention in the drop-down format menu, but after seeing your post I found a couple of cryptic lines in one of the help screens.  I guess that you may be using a more recent version?  

Applying one of the sample custom formats which included 'red' in the string I have already achieved something along the lines I was looking for (all negative values came out red). So again, many thanks.

RE: Displaying cell values in different colours depending on value

Recntly, I've only tried it with excel 97 (ver 8.0?) and excel 2000 (ver 9.0), and I can't remember what would have been done in earlier versions.  Is ver 7.0 commonly called "excel 95?"

Glad to hear that you've got it straightened out.

RE: Displaying cell values in different colours depending on value

(OP)
Hi again. ivymike.

Yes, this came with Office Pro for Windows 95.  

I am glad to report that I have now achieved what I wanted.

This query came from a colleague of mine who mentioned a couple of days ago that he had wanted to display with conditional colours, and didn't know how to do it.

I confidently told him that someone in the eng-tips community would surely know, so you have upheld the honour of the group .

RE: Displaying cell values in different colours depending on value

I just discovered this trick myself.  However, it is limited to three conditions.  Is there a way to write a Visual Basic script or something to increase the number of conditions?

RE: Displaying cell values in different colours depending on value

Yes, you could use some properties for a given cell and then create a loop evaluating the conditions for the color change

With Worksheets("Hoja1").Range("d8")
    Select Case .Value
        Case Is > 100
                .Interior.ColorIndex = 6
        Case Is < 100
                .Interior.ColorIndex = 3
    End Select
End With

RE: Displaying cell values in different colours depending on value

ardilesd: Look i post no. 2 - this is allready "build into" excell.

Best Regards

Morten

RE: Displaying cell values in different colours depending on value

MortenA

That was an answer to Mac7000, how to work around the three conditions, the limit in Excel ...

Regards
Dario

RE: Displaying cell values in different colours depending on value

Forgive my ignorance, but how & where do I use this?  Is it a macro or VB script?

With Worksheets("Hoja1").Range("d8")
    Select Case .Value
        Case Is > 100
                .Interior.ColorIndex = 6
        Case Is < 100
                .Interior.ColorIndex = 3
    End Select
End With

RE: Displaying cell values in different colours depending on value

This is a script that you can attach as a macro to a button or a keyboard short cut. This executes the script and updates your sheet. Go to Macro , Create, and insert the code shown
Regards

RE: Displaying cell values in different colours depending on value

ardilesd: But you dont need to do this - just select the cells that you want to be covered by this tric and the select "format" from the main menu and then "conditional format" from here. The rest should be easy.

I like to program as much as the next guy but this works better.

Best Regards

Morten

RE: Displaying cell values in different colours depending on value

Dear Morten
The problem is not the number of cells but the number of conditions (limited to three conditions in Excel 97 for Windows NT)
Regards

RE: Displaying cell values in different colours depending on value

ivymike...

i've been looking for this min weeks... search finally over... thanks...

RE: Displaying cell values in different colours depending on value

Hey - I am still using Lotus.  Is there a way of coloring cells based on output values?

RE: Displaying cell values in different colours depending on value

Dear Morten,

Say for instance that you wanted the values in your spreadsheet colored four different colors,, red = <0, blue = >0 and <20, green = >20 and <40, black = >40 and <60 etc,
then you can only achieve this in excel by writing a code using  "select case"  as described by ardilesd. The above is not possible using the excel interface, at least not in excel 2000 anyway.

RE: Displaying cell values in different colours depending on value

I've wanted to be able to do this for some time, but it was a low priority for me.

Thanks for the thread.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close