Getting last cell in range

Question

I'm attempting to change the color for a row based on the range from the cell value of "Actual" which is always in column B to the final column which is AX. I was attempting to calculate this in the LastCol line as I was not sure how to begin. at the moment the lastcol line is counting all columns which i understand. How can i limit this to my needed range?

Function Format()
Dim Rng, Rng2, cell, rngtocopy, rngtofinal As Range
Dim LastRow, LastCol As Long
Dim i As Long
Dim ws1 As Worksheet

Set ws1 = Sheets("Cost Sheet")
LastRow = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
LastCol = ws1.Cells(ws1.Columns.Count).End(xlRight).Column
Set Rng = ws1.Range("B12", ws1.Cells(LastRow, "B"))
Set Rng2 = ws1.Range(cell, cell & LastCol)

 For Each cell In Rng
        If cell.Value = "Actual" Then
            cell.EntireRow.Locked = True
                With cell
                    .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 12632256
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
        End If
 Next cell

End Function

First, in `Dim Rng, Rng2, cell, rngtocopy, rngtofinal As Range` only `rngtofinal` will be considered as `Range` and others as `Variant`. You should write it like this `Dim Rng As Range, Rng2 As Range, cell As Range, rngtocopy As Range, rngtofinal As Range`. — simpLE MAn, Mar 13 '14 at 17:55
as I see your `LastCol` used only in line `Set Rng2 = ws1.Range(cell, cell & LastCol)`, but `Rng2` never used — Dmitry Pavliv, Mar 13 '14 at 18:05

score 0 · Accepted Answer · answered Mar 13 '14 at 22:18

0

Found my answer, thank you all for the help it got me to the solution.

LastColumn = Cells.Find(What:="*", After:=[A1], _
                   SearchOrder:=xlByColumns, _
                   SearchDirection:=xlPrevious).Column

answered Mar 13 '14 at 22:18

RL001

169
1
8

Getting last cell in range

1 Answers1