You will need to apply a loop to work through all rows setting up a
condition for each based on the location of the store target.

Something like......exact references will need to change based on your data
location

LastRow = Cells(Rows.Count,1).End(xlup).Row
For xr = 1 to LastRow
With Cells(xr,2)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual,
Formula1:="=$D$" & xr
.FormatConditions(1).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Formula1:="=$D$" & xr
.FormatConditions(2).Interior.ColorIndex = 6
End With
Next xr


--
Cheers
Nigel



"greu" wrote in message
...

I need to compare the row for each store with the target for that store
(and colour the cells in yellow if they are smaller or green if
greater).

Conditional formating helps for few rows, but i have close to 100
stores.

I can record a macro for 1 row, but i need to find a way to
automaticaly do tyhis for all rows.


+-------------------------------------------------------------------+
|Filename: target.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4022 |
+-------------------------------------------------------------------+

--
greu
------------------------------------------------------------------------
greu's Profile:
http://www.excelforum.com/member.php...o&userid=28700
View this thread: http://www.excelforum.com/showthread...hreadid=483837