| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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=565529 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional cell coloring
'----------------------------------------------------------------- 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 = 3 'red Case 2: .Interior.ColorIndex = 6 'yellow Case 3: .Interior.ColorIndex = 5 'blue 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 (replace somewhere in email address with gmail if mailing direct) "phrodude" wrote in message ... 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=565529 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional cell coloring
Hi Bob, Thanks this looks great it's just that I don''t know that much about coding really. I have put this code into corret worksheet code module, but not sure where to go from there. Could you please give me a breif walk through or guide with what to de next please. Thanks in advance, Gavin -- phrodude ------------------------------------------------------------------------ phrodude's Profile: http://www.excelforum.com/member.php...o&userid=36849 View this thread: http://www.excelforum.com/showthread...hreadid=565529 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional cell coloring
Hi Gavin,
What it does is to check whether any cell on the target sheet is changed, and if it is within the range H1:H10, which is defined as a constant at the start so that you can change it, it then checks what value was entered. If it is a 1, it changes the cell colour to red, if a 2 to yellow, etc. Play with it and enter some values in those cells to see the effect, and then just change the range and values and colours to suit. BTW, here is a full colour list Public Enum xlColorIndex xlCIBlack = 1 xlCIWhite = 2 xlCIRed = 3 xlCIBrightGreen = 4 xlCIBlue = 5 xlCIYellow = 6 xlCIPink = 7 xlCITurquoise = 8 xlCIDarkRed = 9 xlCIGreen = 10 xlCIDarkBlue = 11 xlCIDarkYellow = 12 xlCIViolet = 13 xlCITeal = 14 xlCIGray25 = 15 xlCIGray50 = 16 xlCIPeriwinkle = 17 xlCIPlum = 18 xlCIIvory = 19 xlCILightTurquoise = 20 xlCIDarkPurple = 21 xlCICoral = 22 xlCIOceanBlue = 23 xlCIIceBlue = 24 'xlCIDarkBlue = 25 'xlCIPink = 26 'xlCIYellow = 27 'xlCITurquoise = 28 'xlCIViolet = 29 'xlCIDarkRed = 30 'xlCITeal = 31 'xlCIBlue = 32 xlCISkyBlue = 33 xlCILightGreen = 35 xlCILightYellow = 36 xlCIPaleBlue = 37 xlCIRose = 38 xlCILavender = 39 xlCITan = 40 xlCILightBlue = 41 xlCIAqua = 42 xlCILime = 43 xlCIGold = 44 xlCILightOrange = 45 xlCIOrange = 46 xlCIBlueGray = 47 xlCIGray40 = 48 xlCIDarkTeal = 49 xlCISeaGreen = 50 xlCIDarkGreen = 51 xlCIBrown = 53 xlCIIndigo = 55 xlCIGray80 = 56 End Enum -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "phrodude" wrote in message ... Hi Bob, Thanks this looks great it's just that I don''t know that much about coding really. I have put this code into corret worksheet code module, but not sure where to go from there. Could you please give me a breif walk through or guide with what to de next please. Thanks in advance, Gavin -- phrodude ------------------------------------------------------------------------ phrodude's Profile: http://www.excelforum.com/member.php...o&userid=36849 View this thread: http://www.excelforum.com/showthread...hreadid=565529 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional cell coloring
Hi, Bob:
In your code, you put Me.Range(WS_RANGE), what does it mean? Thanks David Bob Phillips wrote: Hi Gavin, What it does is to check whether any cell on the target sheet is changed, and if it is within the range H1:H10, which is defined as a constant at the start so that you can change it, it then checks what value was entered. If it is a 1, it changes the cell colour to red, if a 2 to yellow, etc. Play with it and enter some values in those cells to see the effect, and then just change the range and values and colours to suit. BTW, here is a full colour list Public Enum xlColorIndex xlCIBlack = 1 xlCIWhite = 2 xlCIRed = 3 xlCIBrightGreen = 4 xlCIBlue = 5 xlCIYellow = 6 xlCIPink = 7 xlCITurquoise = 8 xlCIDarkRed = 9 xlCIGreen = 10 xlCIDarkBlue = 11 xlCIDarkYellow = 12 xlCIViolet = 13 xlCITeal = 14 xlCIGray25 = 15 xlCIGray50 = 16 xlCIPeriwinkle = 17 xlCIPlum = 18 xlCIIvory = 19 xlCILightTurquoise = 20 xlCIDarkPurple = 21 xlCICoral = 22 xlCIOceanBlue = 23 xlCIIceBlue = 24 'xlCIDarkBlue = 25 'xlCIPink = 26 'xlCIYellow = 27 'xlCITurquoise = 28 'xlCIViolet = 29 'xlCIDarkRed = 30 'xlCITeal = 31 'xlCIBlue = 32 xlCISkyBlue = 33 xlCILightGreen = 35 xlCILightYellow = 36 xlCIPaleBlue = 37 xlCIRose = 38 xlCILavender = 39 xlCITan = 40 xlCILightBlue = 41 xlCIAqua = 42 xlCILime = 43 xlCIGold = 44 xlCILightOrange = 45 xlCIOrange = 46 xlCIBlueGray = 47 xlCIGray40 = 48 xlCIDarkTeal = 49 xlCISeaGreen = 50 xlCIDarkGreen = 51 xlCIBrown = 53 xlCIIndigo = 55 xlCIGray80 = 56 End Enum -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "phrodude" wrote in message ... Hi Bob, Thanks this looks great it's just that I don''t know that much about coding really. I have put this code into corret worksheet code module, but not sure where to go from there. Could you please give me a breif walk through or guide with what to de next please. Thanks in advance, Gavin -- phrodude ------------------------------------------------------------------------ phrodude's Profile: http://www.excelforum.com/member.php...o&userid=36849 View this thread: http://www.excelforum.com/showthread...hreadid=565529 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
conditional cell coloring
It means the range that is defined in the constant WS_RANGE within the sheet
that the code is contained within (Me). -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Hi, Bob: In your code, you put Me.Range(WS_RANGE), what does it mean? Thanks David Bob Phillips wrote: Hi Gavin, What it does is to check whether any cell on the target sheet is changed, and if it is within the range H1:H10, which is defined as a constant at the start so that you can change it, it then checks what value was entered. If it is a 1, it changes the cell colour to red, if a 2 to yellow, etc. Play with it and enter some values in those cells to see the effect, and then just change the range and values and colours to suit. BTW, here is a full colour list Public Enum xlColorIndex xlCIBlack = 1 xlCIWhite = 2 xlCIRed = 3 xlCIBrightGreen = 4 xlCIBlue = 5 xlCIYellow = 6 xlCIPink = 7 xlCITurquoise = 8 xlCIDarkRed = 9 xlCIGreen = 10 xlCIDarkBlue = 11 xlCIDarkYellow = 12 xlCIViolet = 13 xlCITeal = 14 xlCIGray25 = 15 xlCIGray50 = 16 xlCIPeriwinkle = 17 xlCIPlum = 18 xlCIIvory = 19 xlCILightTurquoise = 20 xlCIDarkPurple = 21 xlCICoral = 22 xlCIOceanBlue = 23 xlCIIceBlue = 24 'xlCIDarkBlue = 25 'xlCIPink = 26 'xlCIYellow = 27 'xlCITurquoise = 28 'xlCIViolet = 29 'xlCIDarkRed = 30 'xlCITeal = 31 'xlCIBlue = 32 xlCISkyBlue = 33 xlCILightGreen = 35 xlCILightYellow = 36 xlCIPaleBlue = 37 xlCIRose = 38 xlCILavender = 39 xlCITan = 40 xlCILightBlue = 41 xlCIAqua = 42 xlCILime = 43 xlCIGold = 44 xlCILightOrange = 45 xlCIOrange = 46 xlCIBlueGray = 47 xlCIGray40 = 48 xlCIDarkTeal = 49 xlCISeaGreen = 50 xlCIDarkGreen = 51 xlCIBrown = 53 xlCIIndigo = 55 xlCIGray80 = 56 End Enum -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "phrodude" wrote in message ... Hi Bob, Thanks this looks great it's just that I don''t know that much about coding really. I have put this code into corret worksheet code module, but not sure where to go from there. Could you please give me a breif walk through or guide with what to de next please. Thanks in advance, Gavin -- phrodude ------------------------------------------------------------------------ phrodude's Profile: http://www.excelforum.com/member.php...o&userid=36849 View this thread: http://www.excelforum.com/showthread...hreadid=565529 |
| 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 | |||
Hybrid Mode
