Here are the first two conditions. For any others just add extra Case
statements with appropriate colorindex.

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

Dim rng As Range

Set rng = Intersect(Target, Range("G:G"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "Completed"
cl.EntireRow.Interior.ColorIndex = 35
Case "Error"
cl.EntireRow.Interior.ColorIndex = 36
Exit Sub
End Select
Next cl
End If

End Sub

It already handles any range in column G, as it loops through each
intersecting cell.. If you want more columns, use

Set rng = Intersect(Target, Range("G:M "))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"cmcfalls " wrote in message
...
Ok, I know the question has been asked several times (trust me I did a
search) but I couldn't find an answer to my question...

I am trying to use VBA to shade cells based on more than 3 conditions.
I think my problem is that I do not know VBA well enough to change code
I have found in other examples to fit my needs.

Here is the example code I am trying to change:

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions

Dim rng As Range

Set rng = Intersect(Target, Range("G:G"))
If rng Is Nothing Then
Exit Sub
Else
Dim cl As Range
For Each cl In rng
Select Case cl.Text
Case "NSP"
cl.EntireRow.Interior.ColorIndex = 35
Case "UAB"
cl.EntireRow.Interior.ColorIndex = 36
Case "IA"
cl.EntireRow.Interior.ColorIndex = 37
Case "AO"
cl.EntireRow.Interior.ColorIndex = 39
Case "US"
cl.EntireRow.Interior.ColorIndex = 40
Case "A"
cl.EntireRow.Interior.ColorIndex = 0
Case Else
cl.EntireRow.Interior.ColorIndex = 0
Exit Sub
End Select
Next cl
End If

End Sub

If I want to change this to do formatting based on a single cell (or
range of cells) to change a single cell (or range of cells), what do I
need to change?

E.g. I am trying to color a single cell (in a range) based on the value
of that same cell. If it says "Completed" I want it green (color index
35), if it says "Error" I want it yellow (color index 36), etc.

Thanks in advance for any help...


---
Message posted from http://www.ExcelForum.com/