I am trying to accomplish a conditional format task.

On sheet3, I have values in Range A5:A100. In this range
are names of stats. On sheet4, I have values in Range
M2:M100. In this range are names of stats as well. Also
on sheet4 I have 2 other ranges. Range P2:P100(Values in
this range are Upper values), and then Range Q2:Q100
(Values in this range are Lower values). What I need is
for the code to look at each stat name on Sheet3 Range
A5:A100, and for each stat it finds to look in Range
Sheet4 M2:M100 and when it finds a match to look in the
corresponding cells in Range P2:P100 and Q2:Q100 and take
those values (Upper and lower values) and go back to
sheet3 to the stat, and offset 1 over into columnB and use
the values in Range P2:P100 and Q2:Q100 as a conditional
format using the color red.

For example...

The code starts running on sheet3 Range A5:A100 and sees
the value "Productivity" in cell A5. The code then goes
to sheet4 and looks for the value "Productivity" in Range
M2:M100. When it finds the value in this range, it needs
to look at the values in the corresponding cell in Range
P2:P100 and Q2:Q100. Lets say the values are 10 and 5
consecutively. The code must then go back to sheet3 where
the value "Productivity" is and then offset 1 over into
the next cell in the column which would be cell B5. The
code then needs to set up a conditional format in that
cell to where any number in that cell over 10 but under 5
will make that cell turn red.

Thanx in advance.

Todd Huttenstine

Sub Macro5()
Dim cell As Range, res As Variant
Worksheets("Sheet3").Activate
For Each cell In Worksheets("sheet3").Range("A5:A8")
res = Application.VLookup(cell.Value, _
Worksheets("sheet4").Range("M2:P100"), 4, False)
res1 = Application.VLookup(cell.Value, _
Worksheets("sheet4").Range("M2:P100"), 3, False)

If Not IsError(res) Then
cell.Offset(0, 1).Select
cell.Offset(0, 1).FormatConditions.Delete
cell.Offset(0, 1).FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlNotBetween, _
Formula1:=res, Formula2:=res1
cell.Offset(0, 1).FormatConditions(1). _
Interior.ColorIndex = 3
End If
Next
End Sub


--
Regards,
Tom Ogilvy

Todd Huttenstine wrote in message
...
I am trying to accomplish a conditional format task.

On sheet3, I have values in Range A5:A100. In this range
are names of stats. On sheet4, I have values in Range
M2:M100. In this range are names of stats as well. Also
on sheet4 I have 2 other ranges. Range P2:P100(Values in
this range are Upper values), and then Range Q2:Q100
(Values in this range are Lower values). What I need is
for the code to look at each stat name on Sheet3 Range
A5:A100, and for each stat it finds to look in Range
Sheet4 M2:M100 and when it finds a match to look in the
corresponding cells in Range P2:P100 and Q2:Q100 and take
those values (Upper and lower values) and go back to
sheet3 to the stat, and offset 1 over into columnB and use
the values in Range P2:P100 and Q2:Q100 as a conditional
format using the color red.

For example...

The code starts running on sheet3 Range A5:A100 and sees
the value "Productivity" in cell A5. The code then goes
to sheet4 and looks for the value "Productivity" in Range
M2:M100. When it finds the value in this range, it needs
to look at the values in the corresponding cell in Range
P2:P100 and Q2:Q100. Lets say the values are 10 and 5
consecutively. The code must then go back to sheet3 where
the value "Productivity" is and then offset 1 over into
the next cell in the column which would be cell B5. The
code then needs to set up a conditional format in that
cell to where any number in that cell over 10 but under 5
will make that cell turn red.

Thanx in advance.

Todd Huttenstine

Change

For Each cell In Worksheets("sheet3").Range("A5:A8")

to

For Each cell In Worksheets("sheet3").Range("A5:A105")

forgot to switch it back after testing.

--
regards,
Tom Ogilvy

Todd Huttenstine wrote in message
...
I am trying to accomplish a conditional format task.

On sheet3, I have values in Range A5:A100. In this range
are names of stats. On sheet4, I have values in Range
M2:M100. In this range are names of stats as well. Also
on sheet4 I have 2 other ranges. Range P2:P100(Values in
this range are Upper values), and then Range Q2:Q100
(Values in this range are Lower values). What I need is
for the code to look at each stat name on Sheet3 Range
A5:A100, and for each stat it finds to look in Range
Sheet4 M2:M100 and when it finds a match to look in the
corresponding cells in Range P2:P100 and Q2:Q100 and take
those values (Upper and lower values) and go back to
sheet3 to the stat, and offset 1 over into columnB and use
the values in Range P2:P100 and Q2:Q100 as a conditional
format using the color red.

For example...

The code starts running on sheet3 Range A5:A100 and sees
the value "Productivity" in cell A5. The code then goes
to sheet4 and looks for the value "Productivity" in Range
M2:M100. When it finds the value in this range, it needs
to look at the values in the corresponding cell in Range
P2:P100 and Q2:Q100. Lets say the values are 10 and 5
consecutively. The code must then go back to sheet3 where
the value "Productivity" is and then offset 1 over into
the next cell in the column which would be cell B5. The
code then needs to set up a conditional format in that
cell to where any number in that cell over 10 but under 5
will make that cell turn red.

Thanx in advance.

Todd Huttenstine

Thanx, below is what I modified the code to be...
What I need is to make an adjustment. The code looks in
columns P and Q. If there is a number in both of the
corresponding cells, then I need for the code to work as
it is using the current conditional formatting method.

Now if there is a number in only the corresponding cell in
columnP, then I need for the conditional format to be make
the cell red if the value is greater than the value in the
cell.

And the other conditional format would be: if there is a
number in only the corresponding cell in columnQ, then I
need for the conditional format to be make the cell red if
the value is less than the value in the cell.


Dim cell As Range, res As Variant
Worksheets(3).Activate
For Each cell In Worksheets(3).Range("A5:A100")
res = Application.VLookup(cell.Value, _
Worksheets(4).Range("M2:Q100"), 4, False)
res1 = Application.VLookup(cell.Value, _
Worksheets(4).Range("M2:Q100"), 5, False)

If Not IsError(res) Then
cell.Offset(0, 1).Select
cell.Offset(0, 1).FormatConditions.Delete
cell.Offset(0, 1).FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlNotBetween, _
Formula1:=res, Formula2:=res1
cell.Offset(0, 1).FormatConditions(1). _
Interior.ColorIndex = 3
End If
Next


-----Original Message-----
Sub Macro5()
Dim cell As Range, res As Variant
Worksheets("Sheet3").Activate
For Each cell In Worksheets("sheet3").Range("A5:A8")
res = Application.VLookup(cell.Value, _
Worksheets("sheet4").Range("M2:P100"), 4, False)
res1 = Application.VLookup(cell.Value, _
Worksheets("sheet4").Range("M2:P100"), 3, False)

If Not IsError(res) Then
cell.Offset(0, 1).Select
cell.Offset(0, 1).FormatConditions.Delete
cell.Offset(0, 1).FormatConditions.Add _
Type:=xlCellValue, _
Operator:=xlNotBetween, _
Formula1:=res, Formula2:=res1
cell.Offset(0, 1).FormatConditions(1). _
Interior.ColorIndex = 3
End If
Next
End Sub


--
Regards,
Tom Ogilvy

Todd Huttenstine
wrote in message
...
I am trying to accomplish a conditional format task.

On sheet3, I have values in Range A5:A100. In this
range
are names of stats. On sheet4, I have values in Range
M2:M100. In this range are names of stats as well.
Also
on sheet4 I have 2 other ranges. Range P2:P100(Values
in
this range are Upper values), and then Range Q2:Q100
(Values in this range are Lower values). What I need is
for the code to look at each stat name on Sheet3 Range
A5:A100, and for each stat it finds to look in Range
Sheet4 M2:M100 and when it finds a match to look in the
corresponding cells in Range P2:P100 and Q2:Q100 and
take
those values (Upper and lower values) and go back to
sheet3 to the stat, and offset 1 over into columnB and
use
the values in Range P2:P100 and Q2:Q100 as a conditional
format using the color red.

For example...

The code starts running on sheet3 Range A5:A100 and sees
the value "Productivity" in cell A5. The code then goes
to sheet4 and looks for the value "Productivity" in
Range
M2:M100. When it finds the value in this range, it
needs
to look at the values in the corresponding cell in Range
P2:P100 and Q2:Q100. Lets say the values are 10 and 5
consecutively. The code must then go back to sheet3
where
the value "Productivity" is and then offset 1 over into
the next cell in the column which would be cell B5. The
code then needs to set up a conditional format in that
cell to where any number in that cell over 10 but under
5
will make that cell turn red.

Thanx in advance.

Todd Huttenstine


.