For/IF problem

[grohm]

HI all

basically, i want to check automatically if a value is bigger than another value and in case it is, mark it with a color.
now i want the whole thing only to take place within a certain area of rows. lets say i want to compare X1 to W1 and X2 to W2 etc and stop on X12 to W12. in case any of the values in the X column are bigger than the compared value in W. i want it to mark that on with a color.

here is what i came up with but somehow it just doesnt work and gives me 1004....


Sub Button1_Click()
Dim afv As Integer
Dim ev As Integer


afv = InputBox("Bitte Anfangs Value eingeben (Row Nr.)")
ev = InputBox("Bitte Endvalue eingeben (Row Nr.)")

For x = afv To ev

If ActiveSheet.Cells(x, afv).Value > ActiveSheet.Cells(w, afv).Value Then
ActiveSheet.Cells(x, afv).Interior.ColorIndex = 4

End If

Next x
End Sub


many thx for all of your help

[nepotist]

Dim i As Integer

For i = 1 To 12
If Sheets("sheet2").Cells(i, 23) > Sheets("sheet2").Cells(1, 24) Then
Sheets("sheet2").Cells(i, 23).Interior.ColorIndex = 4
End If
Next i


End Sub

[mdmackillop]

Hi grohm,
You can do this with Conditional Formatting. No code required.

[mdmackillop]

Hi Nepotist
Should Cells(1, 24) be Cells(i, 24)?

[nepotist]

Yahh.. mybad I am sorry it should be i but not 1..
Good Catch..
:D

[mdmackillop]

Sub Button1_Click()
Dim afv As Integer
Dim ev As Integer
Dim Col As Variant
Dim Rws As String

Rws = InputBox("Bitte Anfangs und Endvalue Values eingeben (Row Nrs.)", , "1,12")
afv = Trim(Split(Rws, ",")(0))
ev = Trim(Split(Rws, ",")(1))

Col = InputBox("Bitte Endvalue eingeben (Column Letter.)",,"W")
Col = Range(Col & 1).Column

For x = afv To ev
If ActiveSheet.Cells(x, Col).Value > ActiveSheet.Cells(x, Col + 1).Value Then
ActiveSheet.Cells(x, Col).Interior.ColorIndex = 4
End If
Next x
End Sub