More than 3 Excel conditional formats? 1

[mtfitzgerald]

(Have searched for this without result...)

I have set up a spreadsheet where the background color changes depending on cell contents. I have used the Excel apparent-maximum of 3 formats. I need 5. Any ideas?

Maurice

 

[xlbo]

From excel L list:
"We have
five categories; Gold = 100, Silver = >=99 to 100, Bronze = >=98 to 99,
Yellow = >=95 to 98, and Red = < 95 to zero. So, To make it simple, <g>
I use something like;
[color44][=100]&quot;GOLD&quot;;[colorXX][>=99]&quot;SILVER&quot;;[black]0; ( XX would be 50%
gray, can't remember the number right now ) The cool part is that the cell
retains the number value versus some text format while the cell displays the
&quot;GOLD&quot; etc., thus you can still due math on it...
I then use conditional formatting for yellow and red to make the fill that
color.&quot;

[color44][=100]&quot;GOLD&quot;;[colorXX][>=99]&quot;SILVER&quot;;[black]0;
is set up in format cells>Custom - please note that this WON'T change the BACKGROUND colour
and the other 2 BACKGROUND colours are set up in conditional formatting

Maybe not quite what you want but does allow a distinction of 5 seperate classes via automatic formatting Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[mtfitzgerald]

As you say, kind of a step in the right direction. I am really looking to format the backgrounds. Must look into conditional copying.

 

[PeterMoran]

Hi mtfitzgerald,

I think this should suit your needs, but it required the use of VBA. I saw it on another bulletin board some time ago and thought it was a great solution - thanks to Ortizmro.

Conditional Formatting – More than three arguments?
colin110 asked this question on 9/29/2001:

Conditional formatting allows three arguments.
Can anyone advise how to increase this to six (for example) arguments?
Or is there a work around that can utilise a Macro to format figures between certain ranges in a similar way to Conditional Formatting?

Cordial regards,
Colin

ortizmro gave this response on 9/29/2001:

Yes, VBA can be used to perform the same function as conditional formatting.
If you open the VBA editor (Alt + F11) and copy/insert this code:

Sub Autpen()
Application.Calculation = xlAutomatic
Worksheets(&quot;Sheet1&quot.OnCalculate = &quot;CheckCells&quot;
End Sub

Sub CheckCells()
Set RangeToFormat = Sheets(&quot;Sheet1&quot.Range(&quot;B7:F17&quot
For Each cell In RangeToFormat
With cell
' Empty cells
If IsEmpty(cell) Then
.Interior.ColorIndex = xlNone
' Numeric cells
ElseIf IsNumeric(cell.Value) Then
Select Case cell.Value
Case Is < 0
.Interior.Color = vbGreen
Case 0
.Interior.Color = vbYellow
Case Is > 0
.Interior.Color = vbMagenta
End Select
' Error cells
ElseIf IsError(cell.Value) Then 'Error cells
.Interior.Color = vbRed
' Other cells (text)
Else
.Interior.ColorIndex = xlNone
End If
End With
Next cell
End Sub

· You need to change the cell reference as needed (currently set to operate on cells B7:F17).
· You need to put a &quot;dummy function&quot; somewhere in the sheet to cause this to execute. Put something like =SUM(B7:F17) in some obscure cell.
· Then save the file, reopen it, and it should work.

Change and/or add Case statements as needed. For help on format of Case statements, place the pointer over the word Case (in the VBA editor) and hit the F1 key.

MRO

colin110 rated this answer:
Fantastic response, Ortizmro.
Many thanks indeed!

Colin

Good Luck!

Peter Moran
Two heads are always better than one!!

 

[mtfitzgerald]

Thanks! That does indeed work. (Given you a star.) I will have to understand the VB method of assigning colors, but that should not be a big deal.

Maurice