Using VBA to evaluate ranges of data

[josh]

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

 

[Otto Moehrbach]

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

 

[Joel]

row 140 is the last row of your worksheet


Private Sub Worksheet_Change(ByVal Target As Range)

if Target.row >= 14 and Target.row <= 150 and _
target.Column >= 3 and Target.Column <= 14 then

If (Target <= Range("P" & target.row).Value And _
Target >= Range("Q" & target.row).Value) Or _
Target = "" Then

Target.Font.Color = RGB(0, 0, 0)
Else
Target.Font.Color = RGB(255, 0, 0)
End if
end if

 

[josh]

Thanks for the responses. Both were helpful.