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

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
news:(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:F8221571-AA91-4CEE-A04D-(E-Mail Removed)...
>> 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
>
>

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

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

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

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

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" <(E-Mail Removed)> wrote in message
news:F8221571-AA91-4CEE-A04D-(E-Mail Removed)...
> 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