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: Sunday, April 22, 2007 3:56 PM 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing cell text color based on cell number
Hi WBW -
Conditional cell formatting might work for you: 1. Select the top, left-most cell in the range. Lets' assume it's cell A10. 2. Choose |Format|Conditional formatting...| 3. Set Condition1 to "Formula Is" and enter this formula: =INT(A10)=A10 Press the [Format...] button and format the font as you wish (Red for Condition1). 4. Press the [Add] button to add a second condtion for blue fonts. 5. Set Condition2 to "Formula Is" and enter this formula: =INT(A10)<A10 Press the [Format...] button and format the font as you wish (Blue for Condition2). 6. Press OK to finish the conditional formatting of Cell A10. 7. Then, apply a number format to cell A10 by pressing the [Decrease Decimal] button on the toolbar. Alternatively, choose |Format|Cells...|. On the Number Tab, choose 'Number' from the Category listbox and set the 'Decimal places' option to 0. 8. Copy the cell (and its formatting) to your range of interest and test it out. --- Jay "W. Wheeler" wrote: From: "W. Wheeler" Subject: Changing cell text color based on cell number Date: Sunday, April 22, 2007 3:56 PM 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 text color based on cell number | Excel Programming | |||
Changing cell or text color depending on week number | Excel Discussion (Misc queries) |