Changing cell text color based on cell number

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

Maybe you want

--
---
HTH

Bob

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Bob Phillips]

-----------------------------------------------------------------
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)

 

[Guest]

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

Thanks!! bunch

 

[W. Wheeler]

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