Conditional Formatting

J

jseger22

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
 
J

JLGWhiz

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
 
J

JLGWhiz

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
 
J

jseger22

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










- 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top