Hi
1. just change the range check in the line
Set rng = Intersect(Target, Range("G:G"))
to your needs. e.g. if you only want to check cell G1 change it to
Set rng = Intersect(Target, Range("G:1))

2. change the select case construct according to syour needs. e.g.
....
Select Case cl.Text
Case "Completed"
cl.Interior.ColorIndex = 35
Case "Error"
cl.Interior.ColorIndex = 36
...
end select
....


--
Regards
Frank Kabel
Frankfurt, Germany

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/