Conditional Formatting of Cell
one other thing, just in case there may not be a value in column D:
Select Case UCase(.Value)
Case "FAIL"
.Offset(, -1).Interior.ColorIndex = 3
Case "WATCH"
.Offset(, -1).Interior.ColorIndex = 19
Case "PASS"
.Offset(, -1).Interior.ColorIndex = 35
Case Else
.Offset(, -1).Interior.ColorIndex = 0
End Select
--
Gary
"Gary Keramidas" <GKeramidasAtMsn.com wrote in message
...
i don't always use conditional formatting unless i'm going to change data
between runs.
so, if you run your query and run code, something like this should work.
Sub test()
Dim i As Long
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To lastrow
With ws.Range("D" & i)
Select Case UCase(.Value)
Case "FAIL"
.Offset(, -1).Interior.ColorIndex = 3
Case "WATCH"
.Offset(, -1).Interior.ColorIndex = 19
Case "PASS"
.Offset(, -1).Interior.ColorIndex = 35
End Select
End With
Next
End Sub
--
Gary
"Dee" wrote in message
...
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
|