Compare rows to col

[greu]

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 |
+-------------------------------------------------------------------+

 

[Nigel]

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

 

[greu]

This macro colors Column B;

i need to color each cell based on a comparaison with the target o
column B


i need to color each cell in the ranges :
C4:H4
C5:H5
...
C9:H9

If i get a macro that works on the file i attached, i will adapt it t
my requirement

 

[greu]

i managed to modify a little to ure code, and it seams to work like a
charm

Here is my code:

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For xr = 1 To LastRow
For col = 3 To 31
With Cells(xr, col)
..FormatConditions.Delete
..FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreaterEqual, Formula1:="=$B$" & xr
..FormatConditions(1).Interior.ColorIndex = 4
..FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Formula1:="=$B$" & xr
..FormatConditions(2).Interior.ColorIndex = 6
End With
Next col
Next xr

Thx a lot

 

[Nigel]

As I said you will need to change the references based on your data set up.
The following code will do it, but you could do it manually for ALL rows at
once by selecting all store rows range C4:H104 (say) then setting the
conditional value formula to ="$B4", this will change to B5....B104 as it
ripples down the sheet. Note if you choose B4 when setting the formula
reference it is entered as $B$4 , remove the 2nd absolute ($) reference.


Sub SetConditions()
Dim LastRow As Long, xr As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
For xr = 4 To LastRow
With Range(Cells(xr, 3), Cells(xr, 8))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual,
Formula1:="=$B$" & xr
.FormatConditions(1).Interior.ColorIndex = 4
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Formula1:="=$B$" & xr
.FormatConditions(2).Interior.ColorIndex = 6
End With
Next xr
End Sub

 

[greu]

I know now; you helped me a lot

Thank you