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