CODEWITHSUNDEEP
excelvba
Neuner
Neuner

Reputation: 85

How to set Column A Between Two Named Rows as a Range?

Struggling to Set the Range in Column A.

The number of rows for the data is always changing so I've named the upper and lower rows of the data set.

If the cell contains text and not a number, I want to change the font to white.

Sub DetailHide()

    Dim RangeToConsider As Range
    Dim shtSrc As Worksheet
    
    Set shtSrc = Sheets("Est Dtl")
    Set RangeToConsider = shtSrc.Range(.Cells(.Range("EstimateDetailHeader").Row + 1, "A"), _
                                       .Cells(.Range("BorderLastRow").Row - 4, "A"))
    For Each Cell In RangeToConsider
        If IsNumeric(Cell) = False Then
            Cell.Select
            Selection.Font.Color = white
        End If
    Next Cell
End Sub

A compile error

Invalid or unqualified reference

occurs at setting the RangeToConsider .Cells(.Range point.

Upvotes: 0

Views: 95

Answers (2)

user3259118
user3259118

Reputation:

I’d like to provide an alternative to the given answer. The code below sets your “RangeToConsider” by first assigning variables to the top and bottom of the range; provides an error trap which tests whether the range is valid or not; and provides the ability to return font to black – should a cell’s value change from text back to numeric. Please give it a try.

Option Explicit
Sub DetailHide()
Dim shtSrc As Worksheet, RangeToConsider As Range, c As Range, t As Long, b As Long

Set shtSrc = Sheets("Est Dtl")

'Define the top (t) and bottom (b) rows of RangeToConsider
t = shtSrc.Range("EstimateDetailHeader").Row + 1
b = shtSrc.Range("BorderLastRow").Row - 1

'Test whether RangeToConsider is a valid range - exit if not
If (b - t) < 2 Then
    MsgBox "Range to consider is less than 1 - exiting sub"
    Exit Sub
End If

Set RangeToConsider = shtSrc.Range(shtSrc.Cells(t, 1), shtSrc.Cells(b, 1))

For Each c In RangeToConsider

    If IsNumeric(c.Value) = False Then
        c.Font.ThemeColor = xlThemeColorDark1
        ElseIf IsNumeric(c.Value) = True Then '<~~ delete this and next line if you wish
            c.Font.ColorIndex = xlAutomatic
    End If

Next c

End Sub

Upvotes: 1

JollyRoger
JollyRoger

Reputation: 329

This code should work:


Sub DetailHide()
    Dim RangeToConsider As Range
    Dim shtSrc As Worksheet
    
    Set shtSrc = Sheets("Est Dtl")
    Set RangeToConsider = shtSrc.Range(shtSrc.Cells(Range("EstimateDetailHeader").Row + 1, "A"), _
                                   shtSrc.Cells(Range("BorderLastRow").Row - 4, "A"))
    For Each Cell In RangeToConsider
        If IsNumeric(Cell) = False Then
            Cell.Select
            With Selection.Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
        End If
    Next Cell
End Sub

Upvotes: 1

Related Questions