Changing cell text color based on cell number
 

I have one column of 50 rows in which I put input Whole numbers. In cell A1
I may input 20. When I input that I may want the text color red or I may want
it to be blue. How can I dictate what color is used with out having to
change the color manually. I was working along the lines of if I enter 20 it
is blue and If I enter 20.1 is red and format the cells so they dislay zero
decimal points. I have not been able to figure this out. Help is Appreciated

Changing cell text color based on cell number
 

Think you need to skip the coloring and look at Data = Validation under the
Data menu. You can restrict cell entry to whole numbers.

--
Regards,
Tom Ogilvy


"scotty" wrote:

I have one column of 50 rows in which I put input Whole numbers. In cell A1
I may input 20. When I input that I may want the text color red or I may want
it to be blue. How can I dictate what color is used with out having to
change the color manually. I was working along the lines of if I enter 20 it
is blue and If I enter 20.1 is red and format the cells so they dislay zero
decimal points. I have not been able to figure this out. Help is Appreciated

Changing cell text color based on cell number
 

Maybe you want

--
---
HTH

Bob

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



"scotty" wrote in message
...
I have one column of 50 rows in which I put input Whole numbers. In cell
A1
I may input 20. When I input that I may want the text color red or I may
want
it to be blue. How can I dictate what color is used with out having to
change the color manually. I was working along the lines of if I enter 20
it
is blue and If I enter 20.1 is red and format the cells so they dislay
zero
decimal points. I have not been able to figure this out. Help is
Appreciated

Changing cell text color based on cell number
 

maybe you want

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 20: .Interior.ColorIndex = 3 'red
Case 20.1: .Interior.ColorIndex = 5 'blue
etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

or maybe even

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

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



"scotty" wrote in message
...
I have one column of 50 rows in which I put input Whole numbers. In cell
A1
I may input 20. When I input that I may want the text color red or I may
want
it to be blue. How can I dictate what color is used with out having to
change the color manually. I was working along the lines of if I enter 20
it
is blue and If I enter 20.1 is red and format the cells so they dislay
zero
decimal points. I have not been able to figure this out. Help is
Appreciated

Changing cell text color based on cell number
 

Thanks. That worked for it initial condition, yet I had to change to font
over interior color. Can you set it up for all numbers or do you have to add
a case line for each value you will be using. Numbers I will be using are
anywhere from 1-100

Thanks

"Bob Phillips" wrote:

maybe you want

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 20: .Interior.ColorIndex = 3 'red
Case 20.1: .Interior.ColorIndex = 5 'blue
etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

or maybe even

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

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



"scotty" wrote in message
...
I have one column of 50 rows in which I put input Whole numbers. In cell
A1
I may input 20. When I input that I may want the text color red or I may
want
it to be blue. How can I dictate what color is used with out having to
change the color manually. I was working along the lines of if I enter 20
it
is blue and If I enter 20.1 is red and format the cells so they dislay
zero
decimal points. I have not been able to figure this out. Help is
Appreciated

Changing cell text color based on cell number
 

What color do you want for 87.3? 49.7? etc.

List all number and color combinations. Remember there are only 56
possible colors in xl2003 and prior.

--
Regards,
Tom Ogilvy


"scotty" wrote:

Thanks. That worked for it initial condition, yet I had to change to font
over interior color. Can you set it up for all numbers or do you have to add
a case line for each value you will be using. Numbers I will be using are
anywhere from 1-100

Thanks

"Bob Phillips" wrote:

maybe you want

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 20: .Interior.ColorIndex = 3 'red
Case 20.1: .Interior.ColorIndex = 5 'blue
etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

or maybe even

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

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



"scotty" wrote in message
...
I have one column of 50 rows in which I put input Whole numbers. In cell
A1
I may input 20. When I input that I may want the text color red or I may
want
it to be blue. How can I dictate what color is used with out having to
change the color manually. I was working along the lines of if I enter 20
it
is blue and If I enter 20.1 is red and format the cells so they dislay
zero
decimal points. I have not been able to figure this out. Help is
Appreciated

Changing cell text color based on cell number
 

Sorry! Any whole number I.E, 3,23,45,67,etc blue
any decimal number I.E.2.4, 12.4,45.9,75.1,etc red

Thanks bunch

"Tom Ogilvy" wrote:

What color do you want for 87.3? 49.7? etc.

List all number and color combinations. Remember there are only 56
possible colors in xl2003 and prior.

--
Regards,
Tom Ogilvy


"scotty" wrote:

Thanks. That worked for it initial condition, yet I had to change to font
over interior color. Can you set it up for all numbers or do you have to add
a case line for each value you will be using. Numbers I will be using are
anywhere from 1-100

Thanks

"Bob Phillips" wrote:

maybe you want

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 20: .Interior.ColorIndex = 3 'red
Case 20.1: .Interior.ColorIndex = 5 'blue
etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

or maybe even

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

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



"scotty" wrote in message
...
I have one column of 50 rows in which I put input Whole numbers. In cell
A1
I may input 20. When I input that I may want the text color red or I may
want
it to be blue. How can I dictate what color is used with out having to
change the color manually. I was working along the lines of if I enter 20
it
is blue and If I enter 20.1 is red and format the cells so they dislay
zero
decimal points. I have not been able to figure this out. Help is
Appreciated

Changing cell text color based on cell number
 

-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
Else
.Interior.Colorindex = 3
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



--
---
HTH

Bob

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



"scotty" wrote in message
...
Sorry! Any whole number I.E, 3,23,45,67,etc blue
any decimal number I.E.2.4, 12.4,45.9,75.1,etc red

Thanks bunch

"Tom Ogilvy" wrote:

What color do you want for 87.3? 49.7? etc.

List all number and color combinations. Remember there are only 56
possible colors in xl2003 and prior.

--
Regards,
Tom Ogilvy


"scotty" wrote:

Thanks. That worked for it initial condition, yet I had to change to
font
over interior color. Can you set it up for all numbers or do you have
to add
a case line for each value you will be using. Numbers I will be using
are
anywhere from 1-100

Thanks

"Bob Phillips" wrote:

maybe you want

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 20: .Interior.ColorIndex = 3 'red
Case 20.1: .Interior.ColorIndex = 5 'blue
etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

or maybe even

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

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



"scotty" wrote in message
...
I have one column of 50 rows in which I put input Whole numbers. In
cell
A1
I may input 20. When I input that I may want the text color red or
I may
want
it to be blue. How can I dictate what color is used with out
having to
change the color manually. I was working along the lines of if I
enter 20
it
is blue and If I enter 20.1 is red and format the cells so they
dislay
zero
decimal points. I have not been able to figure this out. Help is
Appreciated

Changing cell text color based on cell number
 

Thanks!! That appears to work just as I want.

Thanks!! bunch

"Bob Phillips" wrote:

-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
Else
.Interior.Colorindex = 3
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



--
---
HTH

Bob

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



"scotty" wrote in message
...
Sorry! Any whole number I.E, 3,23,45,67,etc blue
any decimal number I.E.2.4, 12.4,45.9,75.1,etc red

Thanks bunch

"Tom Ogilvy" wrote:

What color do you want for 87.3? 49.7? etc.

List all number and color combinations. Remember there are only 56
possible colors in xl2003 and prior.

--
Regards,
Tom Ogilvy


"scotty" wrote:

Thanks. That worked for it initial condition, yet I had to change to
font
over interior color. Can you set it up for all numbers or do you have
to add
a case line for each value you will be using. Numbers I will be using
are
anywhere from 1-100

Thanks

"Bob Phillips" wrote:

maybe you want

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 20: .Interior.ColorIndex = 3 'red
Case 20.1: .Interior.ColorIndex = 5 'blue
etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

or maybe even

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

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



"scotty" wrote in message
...
I have one column of 50 rows in which I put input Whole numbers. In
cell
A1
I may input 20. When I input that I may want the text color red or
I may
want
it to be blue. How can I dictate what color is used with out
having to
change the color manually. I was working along the lines of if I
enter 20
it
is blue and If I enter 20.1 is red and format the cells so they
dislay
zero
decimal points. I have not been able to figure this out. Help is
Appreciated

Changing cell text color based on cell number
 

I like the way this works. But is there some way to make it work if the
Range is derived as the result of a formula. As it stands now I have to
place my cursor in the cell and then it will change. It would be great if
it did it automatically. Any ideas on how to do this?

WBW


"Bob Phillips" wrote in message
...
-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
Else
.Interior.Colorindex = 3
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub



--
---
HTH

Bob

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



"scotty" wrote in message
...
Sorry! Any whole number I.E, 3,23,45,67,etc blue
any decimal number I.E.2.4, 12.4,45.9,75.1,etc red

Thanks bunch

"Tom Ogilvy" wrote:

What color do you want for 87.3? 49.7? etc.

List all number and color combinations. Remember there are only 56
possible colors in xl2003 and prior.

--
Regards,
Tom Ogilvy


"scotty" wrote:

Thanks. That worked for it initial condition, yet I had to change to
font
over interior color. Can you set it up for all numbers or do you have
to add
a case line for each value you will be using. Numbers I will be using
are
anywhere from 1-100

Thanks

"Bob Phillips" wrote:

maybe you want

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 20: .Interior.ColorIndex = 3 'red
Case 20.1: .Interior.ColorIndex = 5 'blue
etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

or maybe even

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Int(.Value) < .Value Then
.Interior.Colorindex = 5
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob

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



"scotty" wrote in message
...
I have one column of 50 rows in which I put input Whole numbers.
In cell
A1
I may input 20. When I input that I may want the text color red or
I may
want
it to be blue. How can I dictate what color is used with out
having to
change the color manually. I was working along the lines of if I
enter 20
it
is blue and If I enter 20.1 is red and format the cells so they
dislay
zero
decimal points. I have not been able to figure this out. Help is
Appreciated