I need a formula which will look at cell D3, and if the formula has returned
a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

Hi Patrick,

"Patrick Simonds" wrote in message
...
I need a formula which will look at cell D3, and if the formula has
returned a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

A formula cannot change the format of another cell.

If you want to use VBA, try:

'============
Public Sub aTester()
Dim rng As Range

Set rng = Selection

With rng.Interior
If Application.IsNumber(Range("D3")) Then
.ColorIndex = 35
Else
.ColorIndex = 0
End If
End With

End Sub
'<<============


---
Regards,
Norman

norman:

i am trying to learn about functions. i came up with this. would this work
correctly?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("d9:f9")
If Application.IsNumber(Range("d3")) Then

.Interior.ColorIndex = 35
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

--


Gary


"Norman Jones" wrote in message
...
Hi Patrick,

"Patrick Simonds" wrote in message
...
I need a formula which will look at cell D3, and if the formula has
returned a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

A formula cannot change the format of another cell.

If you want to use VBA, try:

'============
Public Sub aTester()
Dim rng As Range

Set rng = Selection

With rng.Interior
If Application.IsNumber(Range("D3")) Then
.ColorIndex = 35
Else
.ColorIndex = 0
End If
End With

End Sub
'<<============


---
Regards,
Norman

Use Conditional formatting ---

Select cells D9 to F9. Go to Format Conditional Formatting

In the first drop-down select "Formula Is".

In the text box, enter: =IsNumber($D$3)



"Patrick Simonds" wrote in message
...
I need a formula which will look at cell D3, and if the formula has
returned a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

Hi Gary,

i am trying to learn about functions. i came up with this. would this work
correctly?

Yes, but I would use the worksheet's calculate event.

That said, Jezebel has provided the optimal solution.

---
Regards,
Norman



"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
norman:

i am trying to learn about functions. i came up with this. would this work
correctly?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("d9:f9")
If Application.IsNumber(Range("d3")) Then

.Interior.ColorIndex = 35
Else
.Interior.ColorIndex = 0

End If
End With
End Sub


Gary

It does not seem to work.

There is a formula in cell D3 =IF(C3=0,IF(WEEKDAY($A$1)=2,$A$1),C3+1)
which returns a number (to be more exact, it returns a date, and cell
formatting returns the day number 1, 2, 3, ...15, 16 ect).

The IsNumber seems to return a false because no color is applied to the
cell. I tried swapping the color lines and then it would always change the
cell color.





"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
norman:

i am trying to learn about functions. i came up with this. would this work
correctly?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("d9:f9")
If Application.IsNumber(Range("d3")) Then

.Interior.ColorIndex = 35
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

--


Gary


"Norman Jones" wrote in message
...
Hi Patrick,

"Patrick Simonds" wrote in message
...
I need a formula which will look at cell D3, and if the formula has
returned a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

A formula cannot change the format of another cell.

If you want to use VBA, try:

'============
Public Sub aTester()
Dim rng As Range

Set rng = Selection

With rng.Interior
If Application.IsNumber(Range("D3")) Then
.ColorIndex = 35
Else
.ColorIndex = 0
End If
End With

End Sub
'<<============


---
Regards,
Norman

i just tried it and it worked for me. i put a date in a1, 9/20/05
i put your formula in d3
when i entered a 1 in c3, it formatted d9:f9 in green.

did you put the code on the sheet1 or in a standard module? right click
sheet1, choose view code and paste the code there


if, i'm missing something, let me know
--


Gary


"Patrick Simonds" wrote in message
...
It does not seem to work.

There is a formula in cell D3 =IF(C3=0,IF(WEEKDAY($A$1)=2,$A$1),C3+1)
which returns a number (to be more exact, it returns a date, and cell
formatting returns the day number 1, 2, 3, ...15, 16 ect).

The IsNumber seems to return a false because no color is applied to the
cell. I tried swapping the color lines and then it would always change the
cell color.





"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
norman:

i am trying to learn about functions. i came up with this. would this
work correctly?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("d9:f9")
If Application.IsNumber(Range("d3")) Then

.Interior.ColorIndex = 35
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

--


Gary


"Norman Jones" wrote in message
...
Hi Patrick,

"Patrick Simonds" wrote in message
...
I need a formula which will look at cell D3, and if the formula has
returned a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

A formula cannot change the format of another cell.

If you want to use VBA, try:

'============
Public Sub aTester()
Dim rng As Range

Set rng = Selection

With rng.Interior
If Application.IsNumber(Range("D3")) Then
.ColorIndex = 35
Else
.ColorIndex = 0
End If
End With

End Sub
'<<============


---
Regards,
Norman

Hi Patrick,

It does not seem to work

Using you formula in D3, Gary's code worked for me.

Why, however, not use the conditional format suggestion advanced by
Jezebel - although an instinctive reluctance to trust a solution from a such
named source is, perhaps, understandable.

---
Regards,
Norman


"Patrick Simonds" wrote in message
...
It does not seem to work.

There is a formula in cell D3 =IF(C3=0,IF(WEEKDAY($A$1)=2,$A$1),C3+1)
which returns a number (to be more exact, it returns a date, and cell
formatting returns the day number 1, 2, 3, ...15, 16 ect).

The IsNumber seems to return a false because no color is applied to the
cell. I tried swapping the color lines and then it would always change the
cell color.





"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
norman:

i am trying to learn about functions. i came up with this. would this
work correctly?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("d9:f9")
If Application.IsNumber(Range("d3")) Then

.Interior.ColorIndex = 35
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

--


Gary


"Norman Jones" wrote in message
...
Hi Patrick,

"Patrick Simonds" wrote in message
...
I need a formula which will look at cell D3, and if the formula has
returned a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

A formula cannot change the format of another cell.

If you want to use VBA, try:

'============
Public Sub aTester()
Dim rng As Range

Set rng = Selection

With rng.Interior
If Application.IsNumber(Range("D3")) Then
.ColorIndex = 35
Else
.ColorIndex = 0
End If
End With

End Sub
'<<============


---
Regards,
Norman

Thanks, but a conditional format would always be in place. I am setting this
up to initially set the range color but need to be able to change the color
down the line.

"Jezebel" wrote in message
...
Use Conditional formatting ---

Select cells D9 to F9. Go to Format Conditional Formatting

In the first drop-down select "Formula Is".

In the text box, enter: =IsNumber($D$3)



"Patrick Simonds" wrote in message
...
I need a formula which will look at cell D3, and if the formula has
returned a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

To everyone how took the time my Thanks. It turns out your ideas were good,
it was me. D3 should have been F3 and for the waste of time I am sorry. I
think I will step away and take a break for awhile


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i just tried it and it worked for me. i put a date in a1, 9/20/05
i put your formula in d3
when i entered a 1 in c3, it formatted d9:f9 in green.

did you put the code on the sheet1 or in a standard module? right click
sheet1, choose view code and paste the code there


if, i'm missing something, let me know
--


Gary


"Patrick Simonds" wrote in message
...
It does not seem to work.

There is a formula in cell D3 =IF(C3=0,IF(WEEKDAY($A$1)=2,$A$1),C3+1)
which returns a number (to be more exact, it returns a date, and cell
formatting returns the day number 1, 2, 3, ...15, 16 ect).

The IsNumber seems to return a false because no color is applied to the
cell. I tried swapping the color lines and then it would always change
the cell color.





"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
norman:

i am trying to learn about functions. i came up with this. would this
work correctly?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Range("d9:f9")
If Application.IsNumber(Range("d3")) Then

.Interior.ColorIndex = 35
Else
.Interior.ColorIndex = 0

End If
End With
End Sub

--


Gary


"Norman Jones" wrote in message
...
Hi Patrick,

"Patrick Simonds" wrote in message
...
I need a formula which will look at cell D3, and if the formula has
returned a number then execute the following code:

Range("D9:F9").Select
Selection.Interior.ColorIndex = 35

A formula cannot change the format of another cell.

If you want to use VBA, try:

'============
Public Sub aTester()
Dim rng As Range

Set rng = Selection

With rng.Interior
If Application.IsNumber(Range("D3")) Then
.ColorIndex = 35
Else
.ColorIndex = 0
End If
End With

End Sub
'<<============


---
Regards,
Norman