In accordance with good practice, I'm trying to rewrite sections of my code
that rely on first selecting cells to remove the selection part. Generally
I've had good success with this, but the following has me totally stumped.

I have a series of subs for formatting text along the lines of the example
below:

Sub format_over_105(myRange As Range)

With myRange.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With myRange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

The original code used the formatter like this:
Range("C1").Select
Selection.Value = " 105%"
format_over_105 (Selection.Cells)

It seemed that I should be able to change this to:
Range("C1").Value = " 105%"
format_over_105 (Range("C1").Cells)

However, the second example gives the error 'Object Required' on the second
line, and when I hold the mouse over the line that gave the error, it tells
me
Range("C2").Cells = "Delivery 105%". I have tried using just Range("C1"),
but that gives the same error.

I would have thought that the first and second example were equivalent - can
anyone explain to me why the first works and the second doesn't?

The purpose of the above is simply to provide a 'key' - the formatting of
the actual rows according to their values works fine with
For Each myRow In ActiveSheet.UsedRange.Rows
....
format_over_105 (myRow.Cells)

Many thanks in advance,
Tara H

Depending upon the function format_over_105 try

Range("C1").Value = " 105%"
format_over_105 Range("C1").Cells

or

Range("C1").Value = " 105%"
format_over_105 (Range("C1").Value)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tara H" wrote in message
...
In accordance with good practice, I'm trying to rewrite sections of my
code
that rely on first selecting cells to remove the selection part.
Generally
I've had good success with this, but the following has me totally stumped.

I have a series of subs for formatting text along the lines of the example
below:

Sub format_over_105(myRange As Range)

With myRange.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With myRange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

The original code used the formatter like this:
Range("C1").Select
Selection.Value = " 105%"
format_over_105 (Selection.Cells)

It seemed that I should be able to change this to:
Range("C1").Value = " 105%"
format_over_105 (Range("C1").Cells)

However, the second example gives the error 'Object Required' on the
second
line, and when I hold the mouse over the line that gave the error, it
tells
me
Range("C2").Cells = "Delivery 105%". I have tried using just
Range("C1"),
but that gives the same error.

I would have thought that the first and second example were equivalent -
can
anyone explain to me why the first works and the second doesn't?

The purpose of the above is simply to provide a 'key' - the formatting of
the actual rows according to their values works fine with
For Each myRow In ActiveSheet.UsedRange.Rows
....
format_over_105 (myRow.Cells)

Many thanks in advance,
Tara H

Hi Bob,

Thanks - your first example was exactly what I needed! Coming from a Java
background that seems strange to me - simply removing the brackets from
around 'Range("C1").Cells' to make it work.

Would you mind giving me a quick explanation of what's going on there so I
know for next time?

Many Thanks,
Tara H

"Bob Phillips" wrote:

Depending upon the function format_over_105 try

Range("C1").Value = " 105%"
format_over_105 Range("C1").Cells

or

Range("C1").Value = " 105%"
format_over_105 (Range("C1").Value)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tara H" wrote in message
...
In accordance with good practice, I'm trying to rewrite sections of my
code
that rely on first selecting cells to remove the selection part.
Generally
I've had good success with this, but the following has me totally stumped.

I have a series of subs for formatting text along the lines of the example
below:

Sub format_over_105(myRange As Range)

With myRange.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With myRange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

The original code used the formatter like this:
Range("C1").Select
Selection.Value = " 105%"
format_over_105 (Selection.Cells)

It seemed that I should be able to change this to:
Range("C1").Value = " 105%"
format_over_105 (Range("C1").Cells)

However, the second example gives the error 'Object Required' on the
second
line, and when I hold the mouse over the line that gave the error, it
tells
me
Range("C2").Cells = "Delivery 105%". I have tried using just
Range("C1"),
but that gives the same error.

I would have thought that the first and second example were equivalent -
can
anyone explain to me why the first works and the second doesn't?

The purpose of the above is simply to provide a 'key' - the formatting of
the actual rows according to their values works fine with
For Each myRow In ActiveSheet.UsedRange.Rows
....
format_over_105 (myRow.Cells)

Many thanks in advance,
Tara H

As I said, I had to make assumptions as I don't know what format_over_105
does, but it would seem that it expects a cell(s) reference.
Range("C1").Cells is such, but by enclosing it within parentheses, the
expression was being evaluated before being passed to the routine, hence it
was a value being passed, not the cell(s) reference. The brackets have a
purpose in a call statement, they are not just ways to organise the
statements.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tara H" wrote in message
...
Hi Bob,

Thanks - your first example was exactly what I needed! Coming from a Java
background that seems strange to me - simply removing the brackets from
around 'Range("C1").Cells' to make it work.

Would you mind giving me a quick explanation of what's going on there so I
know for next time?

Many Thanks,
Tara H

"Bob Phillips" wrote:

Depending upon the function format_over_105 try

Range("C1").Value = " 105%"
format_over_105 Range("C1").Cells

or

Range("C1").Value = " 105%"
format_over_105 (Range("C1").Value)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tara H" wrote in message
...
In accordance with good practice, I'm trying to rewrite sections of my
code
that rely on first selecting cells to remove the selection part.
Generally
I've had good success with this, but the following has me totally
stumped.

I have a series of subs for formatting text along the lines of the
example
below:

Sub format_over_105(myRange As Range)

With myRange.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With myRange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

The original code used the formatter like this:
Range("C1").Select
Selection.Value = " 105%"
format_over_105 (Selection.Cells)

It seemed that I should be able to change this to:
Range("C1").Value = " 105%"
format_over_105 (Range("C1").Cells)

However, the second example gives the error 'Object Required' on the
second
line, and when I hold the mouse over the line that gave the error, it
tells
me
Range("C2").Cells = "Delivery 105%". I have tried using just
Range("C1"),
but that gives the same error.

I would have thought that the first and second example were
equivalent -
can
anyone explain to me why the first works and the second doesn't?

The purpose of the above is simply to provide a 'key' - the formatting
of
the actual rows according to their values works fine with
For Each myRow In ActiveSheet.UsedRange.Rows
....
format_over_105 (myRow.Cells)

Many thanks in advance,
Tara H

Thanks Bob.

I included the format_over_105 code in my original post - it expects a
Range, but as I said, my background is in Java, so I am used to putting
parentheses to indicate the parameters to be passed.

I'll bear this in mind for the future. Many thanks again for your help.

Tara H

"Bob Phillips" wrote:

As I said, I had to make assumptions as I don't know what format_over_105
does, but it would seem that it expects a cell(s) reference.
Range("C1").Cells is such, but by enclosing it within parentheses, the
expression was being evaluated before being passed to the routine, hence it
was a value being passed, not the cell(s) reference. The brackets have a
purpose in a call statement, they are not just ways to organise the
statements.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tara H" wrote in message
...
Hi Bob,

Thanks - your first example was exactly what I needed! Coming from a Java
background that seems strange to me - simply removing the brackets from
around 'Range("C1").Cells' to make it work.

Would you mind giving me a quick explanation of what's going on there so I
know for next time?

Many Thanks,
Tara H

"Bob Phillips" wrote:

Depending upon the function format_over_105 try

Range("C1").Value = " 105%"
format_over_105 Range("C1").Cells

or

Range("C1").Value = " 105%"
format_over_105 (Range("C1").Value)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tara H" wrote in message
...
In accordance with good practice, I'm trying to rewrite sections of my
code
that rely on first selecting cells to remove the selection part.
Generally
I've had good success with this, but the following has me totally
stumped.

I have a series of subs for formatting text along the lines of the
example
below:

Sub format_over_105(myRange As Range)

With myRange.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With myRange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

The original code used the formatter like this:
Range("C1").Select
Selection.Value = " 105%"
format_over_105 (Selection.Cells)

It seemed that I should be able to change this to:
Range("C1").Value = " 105%"
format_over_105 (Range("C1").Cells)

However, the second example gives the error 'Object Required' on the
second
line, and when I hold the mouse over the line that gave the error, it
tells
me
Range("C2").Cells = "Delivery 105%". I have tried using just
Range("C1"),
but that gives the same error.

I would have thought that the first and second example were
equivalent -
can
anyone explain to me why the first works and the second doesn't?

The purpose of the above is simply to provide a 'key' - the formatting
of
the actual rows according to their values works fine with
For Each myRow In ActiveSheet.UsedRange.Rows
....
format_over_105 (myRow.Cells)

Many thanks in advance,
Tara H

Tara,

The one I forgot to mention which may be more familiar to you is to call the
procedure, then you use parentheses

Call format_over_105(Range("C1").Cells)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Tara H" wrote in message
...
Thanks Bob.

I included the format_over_105 code in my original post - it expects a
Range, but as I said, my background is in Java, so I am used to putting
parentheses to indicate the parameters to be passed.

I'll bear this in mind for the future. Many thanks again for your help.

Tara H

"Bob Phillips" wrote:

As I said, I had to make assumptions as I don't know what format_over_105
does, but it would seem that it expects a cell(s) reference.
Range("C1").Cells is such, but by enclosing it within parentheses, the
expression was being evaluated before being passed to the routine, hence
it
was a value being passed, not the cell(s) reference. The brackets have a
purpose in a call statement, they are not just ways to organise the
statements.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tara H" wrote in message
...
Hi Bob,

Thanks - your first example was exactly what I needed! Coming from a
Java
background that seems strange to me - simply removing the brackets from
around 'Range("C1").Cells' to make it work.

Would you mind giving me a quick explanation of what's going on there
so I
know for next time?

Many Thanks,
Tara H

"Bob Phillips" wrote:

Depending upon the function format_over_105 try

Range("C1").Value = " 105%"
format_over_105 Range("C1").Cells

or

Range("C1").Value = " 105%"
format_over_105 (Range("C1").Value)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Tara H" wrote in message
...
In accordance with good practice, I'm trying to rewrite sections of
my
code
that rely on first selecting cells to remove the selection part.
Generally
I've had good success with this, but the following has me totally
stumped.

I have a series of subs for formatting text along the lines of the
example
below:

Sub format_over_105(myRange As Range)

With myRange.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With myRange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

The original code used the formatter like this:
Range("C1").Select
Selection.Value = " 105%"
format_over_105 (Selection.Cells)

It seemed that I should be able to change this to:
Range("C1").Value = " 105%"
format_over_105 (Range("C1").Cells)

However, the second example gives the error 'Object Required' on the
second
line, and when I hold the mouse over the line that gave the error,
it
tells
me
Range("C2").Cells = "Delivery 105%". I have tried using just
Range("C1"),
but that gives the same error.

I would have thought that the first and second example were
equivalent -
can
anyone explain to me why the first works and the second doesn't?

The purpose of the above is simply to provide a 'key' - the
formatting
of
the actual rows according to their values works fine with
For Each myRow In ActiveSheet.UsedRange.Rows
....
format_over_105 (myRow.Cells)

Many thanks in advance,
Tara H