Conditional Formatting

A

ADE2

Hi

Is there a way to bypass the limit of three conditions when usin
Conditional Formatting.

I have four outcomes below to which i want to assign different colour
as shown in brackets.

Cell Value Is equal to ="TRENDING" (GREEN)
Cell Value Is equal to ="ABOUT TO TREND" (ORANGE)
Cell Value Is equal to ="NOT TRENDING" (RED)
Cell Value Is equal to ="WEAK TREND" (YELLOW)

Thanks Ad
 
A

ADE2

Have the normal color Green and make a condition for the other three
This will work if the cell will always be one of the four colors
 
D

Dave Peterson

You could use a worksheet_change event:

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in the code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub


Select Case LCase(Target.Value)
Case Is = "trending": myColor = 34
Case Is = "about to trend": myColor = 33
Case Is = "not trending": myColor = 32
Case Is = "weak trend": myColor = 31
Case Else
myColor = xlNone
End Select

Target.Interior.ColorIndex = myColor

End Sub

Adjust the colors to what you want. And adjust the range (I used column A).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you want to learn more about what events are:

Chip Pearson has some notes about events at:
http://www.cpearson.com/excel/events.htm

David McRitchie also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm
 
Top