Syntax error VBA

[gripper]

Hi members,

Question I hope somebody can help me resolve. I am getting the following error "Method 'Range' of object '_Global' failed when I run the following code.
I went over this multiple times and even retyped and still have issues.

Sub color_Row()

Dim Count, i As Long

Count = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

'MsgBox Count

i = 2

Do While i <= Count
  If Cells(i, 9).Value >= 1000 Then
  Range(Cells(i, 1).Cells(i, 20)).Interior.Color = RGB(67, 195, 233)
 
  End If
 
  i = i + 1
 
  Loop



End Sub

I am hoping somebody can give me a hand with this syntax.

The goal of this code is to check column "I" for a value of 1000 or more and then highlight that entire row as it loops though the rows.

Thank you for the help.

 

[StephenCrump]

Try: Range(Cells(1, 1),Cells(1, 20)).Interior.Color = RGB(67, 195, 233)

Rather than looping through each cell, you could simply apply conditional formatting to the range A2:Txx.

 

[gripper]

Try: Range(Cells(1, 1),Cells(1, 20)).Interior.Color = RGB(67, 195, 233)

Rather than looping through each cell, you could simply apply conditional formatting to the range A2:Txx.

That did not work as it just highlighted my header. It is not capturing the loop.

As I step through my code it has an error when it gets to a row that has a value of 1000 or more then it errors.
I cannot use conditional formatting as this code evolves it will pick up data from rows about for calculation and when I transfer to another sheet and merge with other data and sort then conditional formatting fails. I need my row colors to stick when data is sent to another sheet. Conditional formatting works great on the sheet it originated on but fails when transferring.

Thanks though

 

[StephenCrump]

That did not work as it just highlighted my header. It is not capturing the loop.

I meant change just this one line in the code you posted, i.e. change "the "."to a ",

 

[gripper]

That did the trick. You know I looked at that for close to an hour messing with this and never notice I put a period instead of a comma. I am a rookie and miss some of this minor things on occasion that drive me crazy.

Thank you for the help

 

[StephenCrump]

No problem, glad to help. Your VBA coding will get easier with time.