i want to know if there is any solution for this query.

i want vba code to compare values of 2 column. and if value of column 1
is higher than that in second value in col one should turn red. if it
is lower than it should turn green & if it is equal then it should
turn blue.

Thanking in advance

You can do this using conditional formatting: no code required.

Tim

"Jimish" wrote in message
ups.com...
i want to know if there is any solution for this query.

i want vba code to compare values of 2 column. and if value of
column 1
is higher than that in second value in col one should turn red. if
it
is lower than it should turn green & if it is equal then it should
turn blue.

Thanking in advance

yes this is usefull if it is only for one set of col. but if it is for
20 to 25 sets of col. each having 40 to 50 items then it is tough so if
there is any solution for this in vba then it is usefull

Can you restate your problem? Reading your original post it's not really
clear what you need to do, since you only reference column1.

Are you comparing values across two different columns on the same row?
Where do values start and end? Should color only change in the first column?
Are the columns next to each other?

tim

--
Tim Williams
Palo Alto, CA


"Jimish" wrote in message
ups.com...
yes this is usefull if it is only for one set of col. but if it is for
20 to 25 sets of col. each having 40 to 50 items then it is tough so if
there is any solution for this in vba then it is usefull

i am giving u an example here

A1 value is 230 and B1 value is 225
hence i want value in cell A1 to turn red as it is higher.
this is easy to do with conditional formatting option but it is rather
harder and time consuming if it is to be repeated for A1 : A55 and upto
Z1 : Z55.
So i wanted to know whether there is any solution for that available in
vba code.
And thanks for taking out time for my query.
And thanks again in advance for the hopefull solution

and i would also like to know whether it is possible to compare 2 col.
of diffrent sheet and give conditional formating the same way as it is
in the above case. pls give solutions for both diffrently.Tankis once
again.

Bearing in mind that you can apply conditional formatting to an entire
column at once I agree that this is the easiest solution. If you really
didn't want to do it manually you could use a macro to add the
conditional formatting.

It is still unclear if you want to compare column A to B, B to C, C to D
etc or A to B, C to D, E to F etc.
If the former then you the macro would look something like this;

Sub Frmt()
Dim eRow As Long
Dim eCol As Integer
Dim c As Integer
eCol = Cells(1, Columns.Count).End(xlToLeft).Column - 1
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For c = 1 To eCol
With Range(Cells(1, c), Cells(eRow, c))
.Cells(1, c).Activate
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression _
, Formula1:="=RCRC[1]"
.FormatConditions(1).Font.ColorIndex = 3
.FormatConditions.Add Type:=xlExpression _
, Formula1:="=RC<RC[1]"
.FormatConditions(2).Font.ColorIndex = 10
.FormatConditions.Add Type:=xlExpression _
, Formula1:="=RC=RC[1]"
.FormatConditions(3).Font.ColorIndex = 5
End With
Next c
End Sub

Hope this helps
Rowan

Jimish wrote:
i am giving u an example here

A1 value is 230 and B1 value is 225
hence i want value in cell A1 to turn red as it is higher.
this is easy to do with conditional formatting option but it is rather
harder and time consuming if it is to be repeated for A1 : A55 and upto
Z1 : Z55.
So i wanted to know whether there is any solution for that available in
vba code.
And thanks for taking out time for my query.
And thanks again in advance for the hopefull solution

i want the later that is compare a to b, e to f, i to j, m to n etc.
thanks to understand me & giving solution

and also tell if i can compare 2 col of diffrent sheet for eg. colB of
Sheet1 with ColB of sheet2, colE of sheet1 with colE of sheet2 etc.
Thanks in advance

THANKS TIM THE CODE DOES WORK. BUT IT CHANGES THE BACKGROUND COLOUR AND
I WANT TO CHANGE FONT COLOUR IS IT POSSIBLE ?
AND CAN U ALSO PROVIDE SIMILAR CODE TO COMPARE COLS. OF 2 DIFFRENT
SHEET THE SAME WAY AS IT DOES FOR 2 COLS OF SAME SHEET. AND PLS DO
PROVIDE COMMENT AS IT HELPS ME A LOT. THANKS ONCE AGAIN.

ok.
not usng caps now.
i am sorry i made small error in question.
i want to compare cols of 2 diffrent sheet of 2 diff worksheet.
and the previous code of tim is not working it is giving error. of type
mismatch

thanks tim.
but this is giving an error of subscript out of range.

the code i wrote is


Sub tester()
CompareCols ActiveSheet.Range("c3:c50"), _
Workbooks("round+merge.xls").Sheets("sheet7").Rang e("c3:c50")
End Sub


Sub CompareCols(col1 As Range, col2 As Range)
Dim val1, val2, x
col1.Font.ColorIndex = 0
'loop through cells in the first column
For x = 3 To 50 'col1.Cells.Count
val1 = col1.Cells(x).Value
'if cell is not blank
If val1 < "" Then
val2 = col2.Cells(x).Value
Select Case True
Case val1 val2: col1.Cells(x).Font.Color = vbRed
Case val1 < val2: col1.Cells(x).Font.Color = vbGreen
Case val1 = val2: col1.Cells(x).Font.Color = vbBlue
End Select
End If
Next x
End Sub

I bet Tim would want to know what line the error was on.

But subscript out of range usually means you referred to a workbook name that
wasn't open or that worksheet didn't exist in that workbook.


If it's on this line:
Workbooks("round+merge.xls").Sheets("sheet7").Rang e("c3:c50")

make sure you have a workbook named: "round+merge.xls" open (exact spelling)
If that workbook is open, make sure there's a worksheet named "Sheet7".

(or change the names where appropriate.)


Jimish wrote:

thanks tim.
but this is giving an error of subscript out of range.

the code i wrote is

Sub tester()
CompareCols ActiveSheet.Range("c3:c50"), _
Workbooks("round+merge.xls").Sheets("sheet7").Rang e("c3:c50")
End Sub

Sub CompareCols(col1 As Range, col2 As Range)
Dim val1, val2, x
col1.Font.ColorIndex = 0
'loop through cells in the first column
For x = 3 To 50 'col1.Cells.Count
val1 = col1.Cells(x).Value
'if cell is not blank
If val1 < "" Then
val2 = col2.Cells(x).Value
Select Case True
Case val1 val2: col1.Cells(x).Font.Color = vbRed
Case val1 < val2: col1.Cells(x).Font.Color = vbGreen
Case val1 = val2: col1.Cells(x).Font.Color = vbBlue
End Select
End If
Next x
End Sub

--

Dave Peterson

yes now that code works.
but is there any way that when the refrence woksheet is closed then
also the code runs.
and this code work for one col that is C if i want it for others do i
have copy the code and change the col from c to other.
i will tell u the cols that i want are c,f,i,l,o,r,u,x,aa etc.
and there was one problm the colour of c3 & c4 didn't change rest all
worked well

and can u guide me on how to give color i want to give.
cause the color that comes is flourocent and i need seagreen or dark
green.

Hi Jimish,

and can u guide me on how to give color i want to give.
cause the color that comes is flourocent and i need seagreen or dark
green.

Try changing:

Case val1 < val2: col1.Cells(x).Font.Color = vbGreen

to:

Case val1 < val2: col1.Cells(x).Font.ColorIndex = 10

The following macro will add a colour list sheet to the active workbook and
enable you to change the above ColorIndex value to accord with your
requirements:

'===================
Public Sub ColourList()
Dim i As Integer

ActiveWorkbook.Sheets.Add

With ActiveSheet
.Name = "ColourList"
For i = 1 To 56
.Range("A" & i).Value = i
.Range("B" & i).Interior.ColorIndex = i
Next i
End With

End Sub
'<<===================

For more information on the use of colours, see David McRitchie's Colors
page at:

http://www.mvps.org/dmcritchie/excel/colors.htm


---
Regards,
Norman



"Jimish" wrote in message
oups.com...
and can u guide me on how to give color i want to give.
cause the color that comes is flourocent and i need seagreen or dark
green.

yes now i got the color i wanted but what about the query above that.
and one error is coming at the end of the macro type mismatch

You may want to post the current code you're using and indicate which line
causes the error.



Jimish wrote:

yes now i got the color i wanted but what about the query above that.
and one error is coming at the end of the macro type mismatch

--

Dave Peterson

"Jimish" wrote in message
oups.com...
yes now i got the color i wanted but what about the query above that.

Which query? Please try to quote from your posts or the post you're
replying to: it makes the whole process much easier for everyone.
If you mean the question about the workbook being closed , then no, the code
requires the workbook to be open.


and one error is coming at the end of the macro type mismatch


As Dave asked, *where* is the error occurring?

Tim.


--
Tim Williams
Palo Alto, CA

THIS is my code
Sub tester()
CompareCols ActiveSheet.Range("c3:c50"), _
Workbooks("round+merge.xls").Sheets("sheet3").Rang e("c3:c50")
End Sub


Sub CompareCols(col1 As Range, col2 As Range)
Dim val1, val2, x
col1.Font.ColorIndex = 0
loop through cells in the first column
For x = 1 To 50 'col1.Cells.Count
val1 = col1.Cells(x).Value
if cell is not blank
If val1 < "" Then
val2 = col2.Cells(x).Value
Select Case True
Case val1 val2: col1.Cells(x).Font.Color = vbRed
Case val1 < val2: col1.Cells(x).Font.Color = RGB(0, 128, 0)
Case val1 = val2: col1.Cells(x).Font.Color = vbBlue
End Select
End If
Next x

Call tester1

End Sub
now this works well till it comes to cell c49
then it gives the error in the foll. line
"Case val1 val2: col1.Cells(x).Font.Color = vbRed".

and this code work for one col that is C if i want it for others do i
have copy the code and change the col from c to other.
i will tell u the cols that i want are c,f,i,l,o,r,u,x,aa etc.
and there was one problm the colour of c3 & c4 didn't change rest all
worked well

What is in the two cells being compared at the point the code errors?

For testing multiple columns:

Sub tester()
const LAST_COL as integer = 30 'fix to suit
dim r1 as range, r2 as range

set r1 =ActiveSheet.cells("c3:c50"),
set r2
=Workbooks("round+merge.xls").Sheets("sheet3").Ran ge("c3:c50")

do while r1.cells(1).column <= LAST_COL
CompareCols r1,r2
set r1=r1.offset(0,3)
set r2 =r2.offset(0,3)
loop

End Sub

and fix this line
For x = 1 To 50 'col1.Cells.Count
to
For x = 1 To col1.Cells.Count


Tim


"Jimish" wrote in message
ups.com...
THIS is my code
Sub tester()
CompareCols ActiveSheet.Range("c3:c50"), _
Workbooks("round+merge.xls").Sheets("sheet3").Rang e("c3:c50")
End Sub


Sub CompareCols(col1 As Range, col2 As Range)
Dim val1, val2, x
col1.Font.ColorIndex = 0
loop through cells in the first column
For x = 1 To 50 'col1.Cells.Count
val1 = col1.Cells(x).Value
if cell is not blank
If val1 < "" Then
val2 = col2.Cells(x).Value
Select Case True
Case val1 val2: col1.Cells(x).Font.Color = vbRed
Case val1 < val2: col1.Cells(x).Font.Color = RGB(0, 128, 0)
Case val1 = val2: col1.Cells(x).Font.Color = vbBlue
End Select
End If
Next x

Call tester1

End Sub
now this works well till it comes to cell c49
then it gives the error in the foll. line
"Case val1 val2: col1.Cells(x).Font.Color = vbRed".

and this code work for one col that is C if i want it for others do
i
have copy the code and change the col from c to other.
i will tell u the cols that i want are c,f,i,l,o,r,u,x,aa etc.
and there was one problm the colour of c3 & c4 didn't change rest
all
worked well

thanks tim but can do do one more help.
can u tell me where to place this code or rather place this whol code
in one perfect order & then give it to me

Sub tester()
const LAST_COL as integer = 30 'fix to suit
dim r1 as range, r2 as range

set r1 =ActiveSheet.cells("c3:c50"),
set r2 =Workbooks("round+merge.xls").Sheets("sheet3").Ran ge("c3:c50")

do while r1.cells(1).column <= LAST_COL
CompareCols r1,r2
set r1=r1.offset(0,3)
set r2 =r2.offset(0,3)
loop

End Sub

Sub CompareCols(col1 As Range, col2 As Range)
Dim val1, val2, x
col1.Font.ColorIndex = 0
loop through cells in the first column
For x = 1 To col1.Cells.Count
val1 = col1.Cells(x).Value
if cell is not blank
If val1 < "" Then
val2 = col2.Cells(x).Value
Select Case True
Case val1 val2: col1.Cells(x).Font.Color = vbRed
Case val1 < val2: col1.Cells(x).Font.Color = RGB(0, 128, 0)
Case val1 = val2: col1.Cells(x).Font.Color = vbBlue
End Select
End If
Next x
End Sub


--
Tim Williams
Palo Alto, CA


"Jimish" wrote in message
oups.com...
thanks tim but can do do one more help.
can u tell me where to place this code or rather place this whol code
in one perfect order & then give it to me

the code is showing compile error in line

set r1 =ActiveSheet.cells("c3:c50"),
set r2
=Workbooks("round+merge.xls").Sheets("sheet3").Ran ge("c3:c50")

Change
set r1 =ActiveSheet.cells("c3:c50"),
to
Set r1 = ActiveSheet.Range("c3:c50")

Regards
Rowan

Jimish wrote:
the code is showing compile error in line

set r1 =ActiveSheet.cells("c3:c50"),
set r2
=Workbooks("round+merge.xls").Sheets("sheet3").Ran ge("c3:c50")

thanks rowan now the code is working perfect.
i would also like to thanks tim for all his help.
and to others as well those who helped me