Compare rows to col
 

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

Compare rows to col
 

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

Compare rows to col
 

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

--
gre
-----------------------------------------------------------------------
greu's Profile: http://www.excelforum.com/member.php...fo&userid=2870
View this thread: http://www.excelforum.com/showthread.php?threadid=48383

Compare rows to col
 

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 :)


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

Compare rows to col
 

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

--
Cheers
Nigel



"greu" wrote in message
...

This macro colors Column B;

i need to color each cell based on a comparaison with the target on
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 to
my requirements


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

Compare rows to col
 

I know now; you helped me a lot

Thank you


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