Range error

[aimee209]

When I type in the code, I get an error when I try to define the range

Private Sub CommandButton2_Click()
Dim row As Integer
row = 4
Dim Range1 As Range

Range1=("D" & row:"G" & row) ' THIS LINE HAS THE ERROR MESSAGE

For Each a In Range1
If a.Value = "N/A" Then
a.Font.ColorIndex = 1
ElseIf a.Value >= 0.945 Then
a.Font.ColorIndex = 10
ElseIf a.Value > 0.895 Then
a.Font.ColorIndex = 44
ElseIf a.Value >= 0 Then
a.Font.Color = vbRed
Else
a.Font.Color = vbBlack
End If
row.Value = row.Value + 4
Next
End Sub

Thanks!

 

[Don Guillett]

Not quite sure what you are trying to end up with but
Range1=range("D" & row &":"G" & row)
or
range1=range(cells(row,"d"),cells(row,"G"))

======
Private Sub CommandButton2_Click()
Dim row As Integer
row = 4
Dim Range1 As Range

Range1=("D" & row &":"G" & row) ' THIS LINE HAS THE ERROR MESSAGE

For Each a In Range1
If a.Value = "N/A" Then
a.Font.ColorIndex = 1
ElseIf a.Value >= 0.945 Then
a.Font.ColorIndex = 10
ElseIf a.Value > 0.895 Then
a.Font.ColorIndex = 44
ElseIf a.Value >= 0 Then
a.Font.Color = vbRed
Else
a.Font.Color = vbBlack
End If
row.Value = row.Value + 4
Next
End Sub

 

[aimee209]

I figured out that range error, but now I have an error with the line:
row.Value = row.Value+4
It displays as a qualifier error with the second 'row'

Thanks!

 

[Don Guillett]

That's why I asked what you are trying to do??

 

[aimee209]

I want to apply the conditional formatting only to certain part of rows; not
the whole spreadsheet. The formatting would effect D6:G6, D10:G10, D14:G14,
etc.

 

[aimee209]

This is what I currently have and it seems to be working:

Private Sub CommandButton2_Click()

Dim row As Integer
row = 6

Dim Range1 As Range


Do
Set Range1 = Sheets("NAVSUP Enterprise").Range("D" & row & ":G" & row)

For Each a In Range1

If a.Value = "N/A" Then
a.Font.ColorIndex = 1

ElseIf a.Value >= 0.945 Then
a.Font.ColorIndex = 10

ElseIf a.Value > 0.895 Then
a.Font.ColorIndex = 44

ElseIf a.Value >= 0 Then
a.Font.Color = vbRed

Else
a.Font.Color = vbBlack

End If
Next
If row < 75 Then
row = row + 4
Else: End

End If

Loop



End Sub

 

[Don Guillett]

try this
Sub cfeveryfourthrow()
Dim i As Long
For i = 4 To Cells(Rows.Count, "d").End(xlUp).row Step 4
For Each c In Range(Cells(i, "d"), Cells(i, "g"))
Select Case c
Case Is >= 0.0945: mc = 10
Case Is >= 0.0895: mc = 44
Case Is >= 0: mc = 3 'red
Case Else
mc = 1 'black
End Select
c.Font.ColorIndex = mc
Next c

Next i
End Sub