Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell text color based on cell number
From: "W. Wheeler" Subject: Changing cell text color based on cell number Date: Friday, April 13, 2007 10:34 PM 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Cell Background Color based on data from another cell | Excel Discussion (Misc queries) | |||
Changing a color in a cell based on the text in another cell | Excel Discussion (Misc queries) | |||
Changing cell text color based on cell number | Excel Programming | |||
Changing cell or text color depending on week number | Excel Discussion (Misc queries) | |||
changing value of a cell based on another cell color | Excel Discussion (Misc queries) |