Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional cell coloring
Hi, I am creating a large matrix analysis of for my company. I need the value of each cell (0 to 1.0) in my matrix to be displayed in different colors depending on what value band it falls within. I need at least 6 color bands (e.g values less than 0.1 to be blue, values between 0.1 and 0.2 to be green, and so on.). To clarify I need the CELL BACKGROUND color to be displayed and not the text displayed according to what color band the value of the cell is. I know that I can do 3 background colors (color bands) with "conditional formating" but this is just not enough. I can also change the text colors with an "IF" function or "LOOKUP" and get the text to ba changed with all the colors I need. Does anyone know how to do this? or does anyone know how to specify the background color of a cell in a "funtion" [e.g."(green)" fo text color.] as I can then just drop these into my formulae. Thanks A quick response would be great. Gav -- phrodude ------------------------------------------------------------------------ phrodude's Profile: http://www.excelforum.com/member.php...o&userid=36849 View this thread: http://www.excelforum.com/showthread...hreadid=565533 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional cell coloring
phrodude wrote: Hi, I am creating a large matrix analysis of for my company. I need the value of each cell (0 to 1.0) in my matrix to be displayed in different colors depending on what value band it falls within. I need at least 6 color bands (e.g values less than 0.1 to be blue, values between 0.1 and 0.2 to be green, and so on.). To clarify I need the CELL BACKGROUND color to be displayed and not the text displayed according to what color band the value of the cell is. I know that I can do 3 background colors (color bands) with "conditional formating" but this is just not enough. I can also change the text colors with an "IF" function or "LOOKUP" and get the text to ba changed with all the colors I need. Does anyone know how to do this? or does anyone know how to specify the background color of a cell in a "funtion" [e.g."(green)" fo text color.] as I can then just drop these into my formulae. Thanks A quick response would be great. Gav Hi Gav Are the coloured cells' values calculated with a formula or entered by a user? And am I correct in understanding that ONLY the colour is shown and not the actual value (it's the same colour as the cell)? Regards Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional cell coloring
Hi Steve, Well the data is is entered by me the user into sheet1. This matrix is too large to use for analysis so I'm creating a summary one on sheet 2. the formula for the cells is as follows: ='Balance Scorecard xxxxxxxxxx'!D7 (I've replaced my company name with xxxxxxxxxx) At the moment I the cells are *not colored*. I just desire the cell backgrounds to be colored the value/text is *not important * for viewing at the moment. So in otherwords I'd like all the higher valued cell to be colored in warm colors (red orange etc..) and all the low valued cells to be colored in cool colors(blue green etc...). thanks Gav -- phrodude ------------------------------------------------------------------------ phrodude's Profile: http://www.excelforum.com/member.php...o&userid=36849 View this thread: http://www.excelforum.com/showthread...hreadid=565533 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional cell coloring
Hi Gav,
Whilst this may not be an exact fit for what you want, it is code I've used to set cell colours to indicate the status of projects people are working on. It is in 2 sections because the version of Excel I use (97) does not pick up a change when a dropdown selection is used. Regards Fred * *** * Private Sub Worksheet_Calculate() Dim vColor As Long Dim fColor As Long Dim vPattern As Long Dim vPatternColorIndex As Long Dim cell As Range fColor = 1 vColor = 15 vPattern = xlSolid vPatternColorIndex = xlAutomatic If ActiveSheet.Name = "Demand" Then For Each cell In Intersect(Range("A3:IV3"), ActiveSheet.UsedRange) With cell Select Case LCase(.Text) Case "r" vColor = 3 fColor = 2 Case "a" vColor = 44 ' fColor = 1 Case "g" vColor = 10 fColor = 2 Case "n" vColor = 7 fColor = 2 Case "d" vColor = 10 fColor = 2 vPattern = xlLightDown vPatternColorIndex = 2 Case "p" vPatternColorIndex = 2 vPattern = xlLightDown vColor = 41 Case "c" vColor = 5 fColor = 2 Case "x" vColor = 1 fColor = 2 Case "h" vColor = 9 fColor = 2 Case "s" vColor = 9 fColor = 2 vPattern = xlLightDown vPatternColorIndex = 2 Case "" vColor = 15 'xlColorIndexNone Case Else vColor = 15 'xlColorIndexNone fColor = xlColorIndexAutomatic vPattern = xlSolid vPatternColorIndex = xlAutomatic End Select cell.Interior.ColorIndex = vColor cell.Font.ColorIndex = fColor cell.Interior.Pattern = vPattern cell.Interior.PatternColorIndex = vPatternColorIndex End With Next cell End If End Sub * *** * Private Sub Worksheet_Change(ByVal Target As Range) 'Fred Newton, 2004-07-27 Dim vColor As Long Dim fColor As Long Dim vPattern As Long Dim vPatternColorIndexIndex As Long Dim cRange As Range Dim cell As Range '***************** check range **** Set cRange = Intersect(Range("A3:IV3"), (Target(1))) If cRange Is Nothing Then Exit Sub fColor = 1 vPattern = xlSolid vPatternColorIndex = xlAutomatic For Each cell In cRange With cell Select Case LCase(.Text) Case "r" vColor = 3 fColor = 2 Case "a" vColor = 44 Case "g" vColor = 10 fColor = 2 Case "n" vColor = 7 fColor = 2 Case "d" vColor = 10 fColor = 2 vPattern = xlLightDown vPatternColorIndex = 2 Case "c" vColor = 5 fColor = 2 Case "x" vColor = 1 fColor = 2 Case "p" vPatternColorIndex = 2 vPattern = xlLightDown vColor = 41 Case "h" vColor = 9 fColor = 2 Case "s" vColor = 9 fColor = 2 vPattern = xlLightDown vPatternColorIndex = 2 Case "" vColor = 15 'xlColorIndexNone Case Else vColor = 15 'xlColorIndexNone fColor = xlColorIndexAutomatic vPattern = xlSolid vPatternColorIndex = xlAutomatic End Select cell.Interior.ColorIndex = vColor cell.Font.ColorIndex = fColor cell.Interior.Pattern = vPattern cell.Interior.PatternColorIndex = vPatternColorIndex End With Next cell End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional cell coloring
phrodude wrote: Hi Steve, Well the data is is entered by me the user into sheet1. This matrix is too large to use for analysis so I'm creating a summary one on sheet 2. the formula for the cells is as follows: ='Balance Scorecard xxxxxxxxxx'!D7 (I've replaced my company name with xxxxxxxxxx) At the moment I the cells are *not colored*. I just desire the cell backgrounds to be colored the value/text is *not important * for viewing at the moment. So in otherwords I'd like all the higher valued cell to be colored in warm colors (red orange etc..) and all the low valued cells to be colored in cool colors(blue green etc...). thanks Gav Hi Gav Try this: Right-click the tab for Sheet2 View Code and paste Private Sub Worksheet_Activate() Dim c As Range Dim myRange As Range Set myRange = Range(Cells(1, 1), Cells(1, 10)) For Each c In myRange Select Case c.Value Case Is < 0.1 c.Interior.ColorIndex = 55 c.Font.ColorIndex = 55 Case Is < 0.2 c.Interior.ColorIndex = 5 c.Font.ColorIndex = 5 Case Is < 0.3 c.Interior.ColorIndex = 10 c.Font.ColorIndex = 10 Case Is < 0.4 c.Interior.ColorIndex = 50 c.Font.ColorIndex = 50 Case Is < 0.5 c.Interior.ColorIndex = 43 c.Font.ColorIndex = 43 Case Is < 0.6 c.Interior.ColorIndex = 6 c.Font.ColorIndex = 6 Case Is < 0.7 c.Interior.ColorIndex = 44 c.Font.ColorIndex = 44 Case Is < 0.8 c.Interior.ColorIndex = 45 c.Font.ColorIndex = 45 Case Is < 0.9 c.Interior.ColorIndex = 46 c.Font.ColorIndex = 46 Case Else c.Interior.ColorIndex = 3 c.Font.ColorIndex = 3 End Select Next Set c = Nothing Set myRange = Nothing End Sub This macro will colour the cells A1:J1 depending on the value of each cell when the sheet is activated. The colours are selected from Excel's default 56 colour pallette. Hope this will get you started. Regards Steve |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional cell coloring
Hi, well I got this code and it works great, just one thing this works for cell with raw data in them. My cells data comes from a different sheet with the following formula; ='Balance Scorecard xxxxxxxx'!D3 how do I adjust the following code so that it works for my cells with the above input. Thanks Gav -------------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("B2:F2,B4:F16,B19:F72,B74:F97,B99:F110,B112: F120")) Is Nothing Then Select Case Target Case 0 To 10 icolor = 6 Case 10 To 20 icolor = 12 Case 20 To 30 icolor = 7 Case 30 To 40 icolor = 53 Case 40 To 50 icolor = 15 Case 50 To 60 icolor = 42 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub -------------------------------------------------------------------------- -- phrodude ------------------------------------------------------------------------ phrodude's Profile: http://www.excelforum.com/member.php...o&userid=36849 View this thread: http://www.excelforum.com/showthread...hreadid=565533 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
Conditional Formatting For A Cell Other Than The One With The Form | Excel Worksheet Functions | |||
Conditional Formating for a cell based on another cell's value | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |