Instead of:
Range("P14")
Use:
Cells(Target.row, "P")
Do the same for the column Q cell.
HTH Otto

"josh" wrote in message
...
I have written some VBA which looks at 2 specific cells whenever another
cell
is changed and changes the color of the target cell based on whether the
target value is between the 2 other cells (numbers). Although it works
perfectly, I am looking to compact the code. Right now I have a block of
code for each row in a large range (about 100 rows). The 2 numbers which
determine the high and low will always be in the same columns and in the
same
row as the changed cell. Below is a sample of my code. I thought about
using a For Each statement but I'm not sure how I need to write it. Any
ideas?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C14:N14")) Is Nothing Then
If Target <= Range("P14").Value And Target = Range("Q14").Value Or Target
= "" Then
Target.Font.Color = RGB(0, 0, 0)
Else: Target.Font.Color = RGB(255, 0, 0)
End If