Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range error
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range error
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "aimee209" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range error
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! "aimee209" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range error
That's why I asked what you are trying to do??
-- Don Guillett Microsoft MVP Excel SalesAid Software "aimee209" wrote in message ... 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! "aimee209" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range error
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 "aimee209" wrote: 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. "Don Guillett" wrote: That's why I asked what you are trying to do?? -- Don Guillett Microsoft MVP Excel SalesAid Software "aimee209" wrote in message ... 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! "aimee209" wrote: 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range error
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "aimee209" wrote in message ... 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. "Don Guillett" wrote: That's why I asked what you are trying to do?? -- Don Guillett Microsoft MVP Excel SalesAid Software "aimee209" wrote in message ... 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! "aimee209" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subscript out of range error - save copy error | Excel Programming | |||
Range.Select error and screen maximizing error in Workbook_Open() | Excel Programming | |||
Range.Select error and screen maximizing error in Workbook_Open() | Excel Programming | |||
Help with this error Unknown reason for error with Range().Select | Excel Programming | |||
Type Mismatch error & subscript out of range error | Excel Programming |