Hi,

I am trying to conditionally format my worksheet based on the
following conditions:

If cell value = target and cell value = last year's result then
color cell green
If cell value < target and cell value = last years' result then color
cell yellow
If cell value < target and cell value < last year's result then color
cell red

I know you have to do this with vb but I am only a beginner and am not
sure where to start.

Also the target value is stored in a cell but it changes based on the
data element that is looked at. If you could get me started that
would be great.

Thanks,
Jaime

Sub whatColor()
Dim c As Range
target = Range("C2").Value
lyrslts = Range("D2").Value
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set fRng = Worksheets(1).Range("A2:A" & lr)
For Each c In fRng
If c.Value = target And c.Value = lyrslts Then
c.Interior.ColorIndex = 4
ElseIf c.Value < target And c.Value = lyrslts Then
c.Interior.ColorIndex = 6
ElseIf c.Value < target And c.Value < lyrslts Then
c.Interior.ColorIndex = 3
End If
Next
End Sub

" wrote:

Hi,

I am trying to conditionally format my worksheet based on the
following conditions:

If cell value = target and cell value = last year's result then
color cell green
If cell value < target and cell value = last years' result then color
cell yellow
If cell value < target and cell value < last year's result then color
cell red

I know you have to do this with vb but I am only a beginner and am not
sure where to start.

Also the target value is stored in a cell but it changes based on the
data element that is looked at. If you could get me started that
would be great.

Thanks,
Jaime

I hit the trigger too quick there.

Assuming there is a range of values you want to
check located in cells A2 through A(n). Assuming
the Target is a fixed value and the last year's
results is a fixed value. Assuming your range to
check will be of varying length and all data is
on Sheet 1. Change any that are not true.

Sub whatColor()
Dim c As Range
target = Range("C2").Value
lyrslts = Range("D2").Value
lr = Cells(Rows.Count, 1).End(xlUP).Row
Set fRng = Worksheets(1).Range("A2:A" & lr)
For Each c In fRng
If c.Value = target And c.Value = lyrslts Then
c.Interior.ColorIndex = 4
ElseIf c.Value < target And c.Value = lyrslts Then
c.Interior.ColorIndex = 6
ElseIf c.Value < target And c.Value < lyrslts Then
c.Interior.ColorIndex = 3
End If
Next
End Sub

" wrote:

Hi,

I am trying to conditionally format my worksheet based on the
following conditions:

If cell value = target and cell value = last year's result then
color cell green
If cell value < target and cell value = last years' result then color
cell yellow
If cell value < target and cell value < last year's result then color
cell red

I know you have to do this with vb but I am only a beginner and am not
sure where to start.

Also the target value is stored in a cell but it changes based on the
data element that is looked at. If you could get me started that
would be great.

Thanks,
Jaime

On Dec 6, 2:51 pm, JLGWhiz wrote:
I hit the trigger too quick there.

Assuming there is a range of values you want to
check located in cells A2 through A(n). Assuming
the Target is a fixed value and the last year's
results is a fixed value. Assuming your range to
check will be of varying length and all data is
on Sheet 1. Change any that are not true.

Sub whatColor()
Dim c As Range
target = Range("C2").Value
lyrslts = Range("D2").Value
lr = Cells(Rows.Count, 1).End(xlUP).Row
Set fRng = Worksheets(1).Range("A2:A" & lr)
For Each c In fRng
If c.Value = target And c.Value = lyrslts Then
c.Interior.ColorIndex = 4
ElseIf c.Value < target And c.Value = lyrslts Then
c.Interior.ColorIndex = 6
ElseIf c.Value < target And c.Value < lyrslts Then
c.Interior.ColorIndex = 3
End If
Next
End Sub



" wrote:
Hi,

I am trying to conditionally format my worksheet based on the
following conditions:

If cell value = target and cell value = last year's result then
color cell green
If cell value < target and cell value = last years' result then color
cell yellow
If cell value < target and cell value < last year's result then color
cell red

I know you have to do this with vb but I am only a beginner and am not
sure where to start.

Also the target value is stored in a cell but it changes based on the
data element that is looked at. If you could get me started that
would be great.

Thanks,
Jaime- Hide quoted text -

- Show quoted text -

Thanks! This is a great start for me. Actually my target value and
last year value are not fixed. Basically in each row there is a
target, a last years result, and the data from let's say C3:F3. So I
need the conditional formating to work for cells C3:F3: where C1=
target and C2= last year's result. And I need that to iterate for 4
rows.