Too many selects!

[user21136]

Hi,

Could this be simplified please? It's to change the color of some cells that contain formulas.

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Interior.ColorIndex = 10
    Range("C7").Select

Thanks, Glenn

 

[jindon]

Hi
try

With ActiveSheet.Range("a1")
    ActiveSheet.Range(.End(xlDown), .End(xlToRight)) _
    .Interior.ColorIndex = 10
End With

 

[user21136]

Thanks again Jindon

...code is much faster then I had from the recorder!

Cheers Glenn

 

[user21136]

Hi,

This is sort of related to the above - I have been trying to work out how to do it but totally lost!

In the sheet using the code above I change the color of some cells (A3:C7) that contain formulas.

Next to the formulas in A3:C7 I have a column in with I want to write some notes but would also like to change the color of this column too.

In otherwords, using say column C3:C7 which includes formulas, can I offset this to change the color in D3:D7?

I've tried stuff like this but it doesn't work:

        Dim v As Range
        Set v = Range("C65536").End(xlUp)
        With v.Offset(1, 1)
            Range(Selection, Selection.End(xlUp)).Select
            Selection.Interior.ColorIndex = 17
        End With

Thanks for any help.

Cheers, Glenn

Edit: the reason why I'm using the End(xlUp) is because the number of formulas vary down the columns in A B and C.