Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
I would like to test a cell (A1) for a number between 1 and 9.
Depending on the value returned I would like to format the cell (A1) with a specific color Example: If A1 =1 then format cell yellow If A1 = 2 then format cell blue If A1 = 3 then format cell red If A1 = 4 then format cell green and so on to 9 Where do go to find the standard colors and their respective color number? Thanks for any help Emile |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
'----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" 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 1: .Interior.ColorIndex = 6 'yellow Case 2: .Interior.ColorIndex = 5 'blue Case 3: .Interior.ColorIndex = 3 'red Case 4: .Interior.ColorIndex = 10 'green End Select 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 Phillips (remove nothere from email address if mailing direct) "Emile" wrote in message ... I would like to test a cell (A1) for a number between 1 and 9. Depending on the value returned I would like to format the cell (A1) with a specific color Example: If A1 =1 then format cell yellow If A1 = 2 then format cell blue If A1 = 3 then format cell red If A1 = 4 then format cell green and so on to 9 Where do go to find the standard colors and their respective color number? Thanks for any help Emile |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
Bob:
Thank you very much. Works great as long as the cell value (1-9) is typed in. In your example cell H1. Unfortunately, my H1 value is a calculated value [Sum(f4:F31)] which changes as more values are added in F4:F31 Is ther a way to automatically update the color as the value in the cell changes. Thanks again |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
Emile, Try this then
'----------------------------------------------------------------- Private Sub Worksheet_Calculate() '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" Dim cell As Range For Each cell In Me.Range(WS_RANGE) With cell Select Case .Value Case 1: .Interior.ColorIndex = 6 'yellow Case 2: .Interior.ColorIndex = 5 'blue Case 3: .Interior.ColorIndex = 3 'red Case 4: .Interior.ColorIndex = 10 'green Case Else: .Interior.ColorIndex = xlColorIndexNone End Select End With Next cell End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Emile" wrote in message ... Bob: Thank you very much. Works great as long as the cell value (1-9) is typed in. In your example cell H1. Unfortunately, my H1 value is a calculated value [Sum(f4:F31)] which changes as more values are added in F4:F31 Is ther a way to automatically update the color as the value in the cell changes. Thanks again |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
Bob:
That worked perfect! Thank you. In an effort to get help with this question (which I could never have figured out without your help) I simplified my question somewhat. The numbers 1-9 as previously mentioned are actually not whole numbers. They have two decimal places. So when the sum (that I am evaluating) is anything more than a whole number in it, I get no color at all. Example 4.65 returns no color Example 4.00 returns the assigned color Sorry to be a bother, but your help is greatly appreciated. Emile |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
Maybe this, change the colours to suit
'----------------------------------------------------------------- Private Sub Worksheet_Calculate() '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" Dim cell As Range For Each cell In Me.Range(WS_RANGE) With cell If .Value = 1 And .Value < 10 Then Select Case .Value Case Is 9: .Interior.ColorIndex = 10 'green Case Is 8: .Interior.ColorIndex = 3 'red Case Is 7: .Interior.ColorIndex = 5 'blue Case Is 6: .Interior.ColorIndex = 6 'yellow Case Is 5: .Interior.ColorIndex = 6 'yellow Case Is 4: .Interior.ColorIndex = 10 'green Case Is 3: .Interior.ColorIndex = 3 'red Case Is 2: .Interior.ColorIndex = 5 'blue Case Is 1: .Interior.ColorIndex = 6 'yellow Case Else: .Interior.ColorIndex = xlColorIndexNone End Select End If End With Next cell End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Emile" wrote in message ... Bob: That worked perfect! Thank you. In an effort to get help with this question (which I could never have figured out without your help) I simplified my question somewhat. The numbers 1-9 as previously mentioned are actually not whole numbers. They have two decimal places. So when the sum (that I am evaluating) is anything more than a whole number in it, I get no color at all. Example 4.65 returns no color Example 4.00 returns the assigned color Sorry to be a bother, but your help is greatly appreciated. Emile |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
Bob:
Works exactly the way I want. Thank you again - would be lost without your (and all people who support these groups) assistance. Emile |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional formatting for more than 3 variables
I'm going to remember that page and recommend it more often <vbg
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "David McRitchie" wrote in message ... For help with the color index numbers see http://www.mvps.org/dmcritchie/excel/colors.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - Formulas | Excel Discussion (Misc queries) | |||
Conditional Formatting when inserting a row | Excel Worksheet Functions | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
difficulty with conditional formatting | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |