3 replies
by illilli
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.
by rider68
by haygal
This discussion is closed to new replies.
Trending Web Development posts
Sort by Trending