Displaying cell values in different colours depending on value 5

[austim]

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'

 

[ivymike]

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.

 

[austim]

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.

 

[ivymike]

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.

 

[austim]

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 .

 

[Mac7000]

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?

 

[ardilesd]

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&quot.Range("d8&quot
Select Case .Value
Case Is > 100
.Interior.ColorIndex = 6
Case Is < 100
.Interior.ColorIndex = 3
End Select
End With

 

[MortenA]

ardilesd: Look i post no. 2 - this is allready &quot;build into&quot; excell.

Best Regards

Morten

 

[ardilesd]

MortenA

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

Regards
Dario

 

[Mac7000]

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

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

 

[ardilesd]

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

 

[MortenA]

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

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

Best Regards

Morten

 

[ardilesd]

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

 

[SahraniKasim]

ivymike...

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

 

[MPENN]

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

 

[mkoijn]

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 &quot;select case&quot; as described by ardilesd. The above is not possible using the excel interface, at least not in excel 2000 anyway.

 

[Stumpy]

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

Thanks for the thread.