Thanks All, for replying. All were very good solutions. I decided to use
Gary's suggestion in my program, it was the easiest to follow. Others will
have to maintain code, it will take less time explaining to others. Thanks
again, you all are save me a lot of time.
--
DC


"FSt1" wrote:

hi
you will have to have some way of telling vb what range to apply the cf to.
you could add an input box so that the code will ask you what range.
Dim myRange As Range
Dim r As String
r = InputBox("enter range")
Set myRange = Range(r) 'Range("C1:C10")
With myRange
.ect

or if you don't want to be bothered by input, you could do this.

Dim myRange As Range
Dim r As Long
r = Cells(Rows.Count, "C").End(xlUp).Row
Set myRange = Range("C1:C" & r)
with myrange
.ect
this way vb would just find the last row and use it in the range.

regards
FSt1

"Dee" wrote:

Barb, is there a way to code the range for the statement "Set myRange =
Range("C1:C10") " my db will update each month and add rows. Dont want to
have to change code every month.

Thanks,
DC

--
DC


"Barb Reinhardt" wrote:

Try this. It will set the conditional format of the cells in the range based
upon the value in the cell to the RIGHT.

Dim myRange As Range
Set myRange = Range("C1:C10")
With myRange
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RC[1]=""PASS"""
.FormatConditions(1).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RC[1]=""WATCH"""
.FormatConditions(2).Interior.ColorIndex = 6
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RC[1]=""FAIL"""
.FormatConditions(3).Font.ColorIndex = 2
.FormatConditions(3).Interior.ColorIndex = 3
End With

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"Dee" wrote:

Hello, I have a worksheet whos data comes from SQL Server db. Autofilter is
turned on. Within the worksheet are many columns. I would like to change
the backcolor of the cells in column C based on the content of the
cell in column D (could be "PASS", "WATCH", or "FAIL") on the same row
(i.e. c1=5 d1='Pass' change backcolor of c1
to Green, or c1=7 d1='WATCH' change backcolor to Yellow, or c1=10 d1='FAIL'
change backcolor to Red). How do I identify the range and apply correct
backcolor to cells within range? Need to be coded using vba macro? --
DC

--
Dee