Applying style to row, getting Run-time error '450'

[Dustin Carter]

I'm attempting to apply a style to a row in Excel 2000 based on a validated
cell at the end of the row (containing the style type). My clunky method for
doing so, at this point, involves:

- having a style for each column in the row, for example:
"SubSection - 1"
"SubSection - 2"
"SubSection - 3"
- getting the data out of the validated cell
- setting the desired row as a range
- looping through each cell and applying the style type ("SubSection - ")
plus the column number as the style.

This is probably inefficient, but the real problem I'm encountering at this
point is with the last step. The code is as follows:

For Each cell In rngRow
cell.Style = strStyle & cell.Column
Next cell

Seems simple, seems like it should work, but produces a Run-time error
'450'. No idea why, no idea how to fix it. Hopefully one of the great Excel
Programming minds holds the answer...

Thanks,
Dustin Carter

[Tom Ogilvy]

I only created one style, SubSection - 6.

Then this worked for me:

Sub Tester1()
Dim rngRow As Range, strStyle As String
Dim cell As Range
Set rngRow = Range("A10:I10")
Cells(rngRow.Row, 256).Value = "SubSection - "
strStyle = Cells(rngRow.Row, 256).Value
For Each cell In rngRow
If cell.Column = 6 Then
cell.Style = strStyle & cell.Column
End If
Next cell

End Sub
--
Regards,
Tom Ogilvy


"Dustin Carter" <[email protected]> wrote in message
news:[email protected]...
> I'm attempting to apply a style to a row in Excel 2000 based on a
validated
> cell at the end of the row (containing the style type). My clunky method
for
> doing so, at this point, involves:
>
> - having a style for each column in the row, for example:
> "SubSection - 1"
> "SubSection - 2"
> "SubSection - 3"
> - getting the data out of the validated cell
> - setting the desired row as a range
> - looping through each cell and applying the style type ("SubSection - ")
> plus the column number as the style.
>
> This is probably inefficient, but the real problem I'm encountering at
this
> point is with the last step. The code is as follows:
>
> For Each cell In rngRow
> cell.Style = strStyle & cell.Column
> Next cell
>
> Seems simple, seems like it should work, but produces a Run-time error
> '450'. No idea why, no idea how to fix it. Hopefully one of the great
Excel
> Programming minds holds the answer...
>
> Thanks,
> Dustin Carter

[Dustin Carter]

Hey Tom,
Thanks for the help. I'm not sure what I ended up changing but things are
working great now. If you get the chance, could you make sure I'm not doing
anything terribly stupid with this sub?

Sub ApplyStyle()
Dim ws As Worksheet: Set ws = ActiveSheet

' The Style named range is the column containing the desired style.
Dim rngStyleID As Range: Set rngStyleID = ws.Range("Style")

Dim strStyleID As String: Dim strStyle As String
Dim rngRow As Range: Dim cell As Range
Dim iRow As Integer: Dim iCol As Integer

For iRow = 1 To rngStyleID.Rows.Count Step 1
Set rngRow = ws.Range(Cells(rngStyleID.Rows(iRow).Row, 1),
_ Cells(rngStyleID.Rows(iRow).Row, 5))

Select Case rngStyleID(iRow).Value
Case "Section"
strStyle = "TRC - Section - "
Case "Section Note"
strStyle = "TRC - Section Note - "
Case Else
strStyle = "Invalid"
End Select

If strStyle <> "Invalid" Then
For Each cell In rngRow
cell.Style = strStyle & cell.Column
Next cell
End If
Next iRow
End Sub

Thanks again,
Dustin Carter

"Tom Ogilvy" wrote:

> I only created one style, SubSection - 6.
>
> Then this worked for me:
>
> Sub Tester1()
> Dim rngRow As Range, strStyle As String
> Dim cell As Range
> Set rngRow = Range("A10:I10")
> Cells(rngRow.Row, 256).Value = "SubSection - "
> strStyle = Cells(rngRow.Row, 256).Value
> For Each cell In rngRow
> If cell.Column = 6 Then
> cell.Style = strStyle & cell.Column
> End If
> Next cell
>
> End Sub
> --
> Regards,
> Tom Ogilvy
>
>
> "Dustin Carter" <[email protected]> wrote in message
> news:[email protected]...
> > I'm attempting to apply a style to a row in Excel 2000 based on a
> validated
> > cell at the end of the row (containing the style type). My clunky method
> for
> > doing so, at this point, involves:
> >
> > - having a style for each column in the row, for example:
> > "SubSection - 1"
> > "SubSection - 2"
> > "SubSection - 3"
> > - getting the data out of the validated cell
> > - setting the desired row as a range
> > - looping through each cell and applying the style type ("SubSection - ")
> > plus the column number as the style.
> >
> > This is probably inefficient, but the real problem I'm encountering at
> this
> > point is with the last step. The code is as follows:
> >
> > For Each cell In rngRow
> > cell.Style = strStyle & cell.Column
> > Next cell
> >
> > Seems simple, seems like it should work, but produces a Run-time error
> > '450'. No idea why, no idea how to fix it. Hopefully one of the great
> Excel
> > Programming minds holds the answer...
> >
> > Thanks,
> > Dustin Carter
>
>
>