comapre 2 col.

[Jimish]

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

 

[Tim Williams]

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

Tim

 

[Jimish]

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

 

[Tim Williams]

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

 

[Jimish]

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

 

[Jimish]

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.

 

[Rowan]

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:="=RC>RC[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]

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

 

[Jimish]

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

 

[Tim Williams]

'************************************************
Option Explicit


Sub tester()
CompareCols ActiveSheet.Range("A1:A100")
End Sub



'by default comparison will be with next column over, unless
' coloffset parameter is passed
Sub CompareCols(col1 As Range, Optional ByVal coloffset As Integer)

Dim c As Range, val

If coloffset < 1 Then coloffset = 1
col1.Interior.ColorIndex = xlNone
For Each c In col1.Cells
If c.Value <> "" Then
val = c.Offset(0, coloffset).Value
Select Case True
Case c.Value > val: c.Interior.Color = vbRed
Case c.Value < val: c.Interior.Color = vbGreen
Case c.Value = val: c.Interior.Color = vbBlue
End Select
End If

Next c

End Sub
'******************************************


Tim.

 

[Jimish]

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.

 

[Tim Williams]

Please don't post in all caps: it's the same as SHOUTING.

Try this

'************************************************
Option Explicit


Sub tester()
CompareCols ActiveSheet.Range("A1:A100"), _
Thisworkbook.sheets("other sheet").range("B1:B100")
End Sub


Sub CompareCols(col1 As Range, col2 as range)

Dim val1,val2,x

col1.font.ColorIndex = xlNone
'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: c.font.Color = vbRed
Case val1< val2: c.font.Color = vbGreen
Case val1 = val2: c.font.Color = vbBlue
End Select
End If

Next x

End Sub
'******************************************

 

[Jimish]

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

 

[Tim Williams]

Hmm. I shouldn't post untested code. This works for me.

Tim.


'***************************************************
Option Explicit

Sub tester()
CompareCols ActiveSheet.Range("A1:A100"), _
Workbooks("other.xls").Sheets("othersheet").Range("B1:B100")
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 = vbGreen
Case val1 = val2: col1.Cells(x).Font.Color = vbBlue
End Select
End If
Next x
End Sub

 

[Jimish]

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

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

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

 

[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.

 

[Norman Jones]

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:

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

 

[Jimish]

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