comapre 2 col.

[Dave Peterson]

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

 

[Tim Williams]

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.

 

[Jimish]

THIS is my code
Sub tester()
CompareCols ActiveSheet.Range("c3:c50"), _
Workbooks("round+merge.xls").Sheets("sheet3").Range("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

 

[Tim Williams]

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").Range("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]

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

 

[Tim Williams]

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").Range("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

 

[Jimish]

the code is showing compile error in line

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

 

[Rowan]

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

Regards
Rowan

 

[Jimish]

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