General discussion

Locked

Can you have more than 3 conditional formats in Excel?

By kz2000 ·
Excel allows for only 3 conditional formats - that I can see. I have a client who wants to know if you can have more than 3 conditional formats to a worksheet. Anyone have any ideas?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

With VBA, conditional formats are unlimited

by illilli In reply to Can you have more than 3 ...

I have researched this issue at great length. You are limited to 3 conditional formats using the conditional format tool through the Excel menu. You can however create VBA code that will give you unlimited access to conditional formatting. The downside is that you have to enable macros before this will work. (If someone knows how to get more than 3 or a way to simulate more than 3, please admonish me and post the method because I would love to know.)

I guess you want me to post some code for you to look at, don't you? Well, even if you don't, here is an example that I have used to change the colors of the cell based on content:

Sub ColorCodeMetrics()

Dim LastRow As Integer
Dim LastCol As Integer
Dim CurrentRow As Integer
Dim CurrentCol As Integer

Sheets("Sheet2").Activate
Range("A1").Select
Selection.End(xlDown).Select
LastRow = ActiveCell.Row
Range("A1").Select
Selection.End(xlToRight).Select
LastCol = ActiveCell.Column
Range("C2", Cells(LastRow, LastCol)).Select
Selection.Interior.ColorIndex = 0

For Each c In Range("C2", Cells(LastRow, LastCol))
CurrentRow = c.Row
CurrentCol = c.Column
If Cells(CurrentRow, CurrentCol) = "0.00%" Then
Cells(CurrentRow, CurrentCol).Interior.ColorIndex = 35
ElseIf Cells(CurrentRow, CurrentCol) = "" Then
Cells(CurrentRow, CurrentCol).Interior.ColorIndex = 15
ElseIf CDbl(Left(Cells(CurrentRow, CurrentCol), Len(Cells(CurrentRow, CurrentCol)) - 1)) > 2.99 Then
Cells(CurrentRow, CurrentCol).Interior.ColorIndex = 38
ElseIf CDbl(Left(Cells(CurrentRow, CurrentCol), Len(Cells(CurrentRow, CurrentCol)) - 1)) > 1.99 Then
Cells(CurrentRow, CurrentCol).Interior.ColorIndex = 19
Else
Cells(CurrentRow, CurrentCol).Interior.ColorIndex = 0
End If
Next c

End Sub

This was coded for Excel 2000 SP-3. I hope this helps you.

Collapse -

there is an answer to your question

by rider68 In reply to With VBA, conditional for ...

To get round excel's limits of just 3 conditions, There is an Addin thats available which will allow you to set up to 30 conditions, The only drawback is if the work book is shared, Others in the group need to have the addin as well, otherwise the conditions are not recognised, If you would like to try for yourself please let me know and i can email you, Regards

Collapse -

Addin for conditional formatting

by haygal In reply to there is an answer to you ...

Can you forward the addin for the expanded conditional formatting. Thanks

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums