IsNumber question
 

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

IsNumber question
 

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

IsNumber question
 

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

IsNumber question
 

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

IsNumber question
 

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

IsNumber question
 

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

IsNumber question
 

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

IsNumber question
 

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

IsNumber question
 

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

IsNumber question
 

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