vb problem

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hello
I'm trying to change the color of the cells starting in the 3rd row if the
cell is > cell (J1)
the macro stops at the "If Selected.Value > ActiveSheet.Cells(1, 10) Then"
I can't get past this point.
Please Help


Sub targetamount()

Dim currentrow As Integer
Dim currentcolumn As Integer

currentrow = 3
Set rowfirstcell = ActiveSheet.Cells(currentrow, 1)

While Trim(rowfirstcell.Value) <> ""
For currentcolumn = 3 To 9
Set selectedcell = ActiveSheet.Cells(currentrow, currentcolumn)
If Selected.Value > ActiveSheet.Cells(1, 10) Then
selectedcell.Interior ColorIndex = 37
Else
selectedcell.Interior ColorIndex = 2
End If
Next

Wend


End Sub




Thank You
first timer :}
 
Jim
The line:
If Selected.Value > ActiveSheet.Cells(1, 10) Then
Should be:
If Selection.Value > Cells(1, 10).Value Then

HTH Otto
 
the macro stops at the "If Selected.Value > ActiveSheet.Cells(1, 10) Then"

What is "Selected.Value" supposed to be? Do you mean "selectedcell.Value"?

Also:
While Trim(rowfirstcell.Value) <> ""
Click to expand...
Since I don't see how this value will ever change within your For...Next
loop (you aren't changing any values at all), you will never exit the
While...Wend infinite loop.
You could simply replace your While...Wend structure with:
If Trim(rowfirstcell.Value) = "" Then Exit Sub
For currentcolumn = 3 To 9
etc


HTH,
 
Some changes...

Sub targetamount()

Dim currentrow As Integer
Dim currentcolumn As Integer

currentrow = 3
Set rowfirstcell = Cells(currentrow, 1)

IF Trim(rowfirstcell.Value) <> "" THEN
For currentcolumn = 3 To 9
WITH Cells(currentrow, currentcolumn)
If .Value > Cells(1, 10) Then
.Interior.ColorIndex = 37
Else
.Interior.ColorIndex = 2
End If
END WITH
Next

END IF
 
Sub targetamount()
Dim rowfirstcell as Range
Dim currentrow As Long
Dim currentcolumn As Integer
Dim selectedcell as Range

currentrow = 3
Set rowfirstcell = ActiveSheet.Cells(currentrow, 1)

While Trim(rowfirstcell.Value) <> ""
For currentcolumn = 3 To 9
Set selectedcell = ActiveSheet.Cells(currentrow, currentcolumn)
If Selectedcell.Value > ActiveSheet.Cells(1, 10) Then
selectedcell.Interior ColorIndex = 37
Else
selectedcell.Interior ColorIndex = 2
End If
Next
set rowfirstcell = rowfirstcell.offset(1,0)
currentrow = rowfirstcell.row
Wend
End Sub
 
Back
Top