| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cell Shading Colour Property
Since changing the color in a cell does not trigger calculation the
"volatile" is superfluous. The originator of the code used it and I have not bothered to remove. Gord Dibben MS Excel MVP On Wed, 16 Sep 2009 21:34:14 +0100, "Tim Childs" wrote: Hi Gord that's just the ticket! thanks v much. pl can you just explain the volatile at the beginning: I did not have that in my basic function and I do get some problems (e.g. get the #NAME erro initially and have to force recalculation) when copying the function to other workbooks (after moving the module into the other book, of course) finally, why do plain cells have an index of -4142 not a small positive integer? thanks Tim "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Not official as Rick states but these are close. =CellColor(A1,true) Note: if colors have been modified from default...............all goes out the window<g Function CellColor(rCell As Range, Optional ColorName As Boolean) Dim strColor As String, iIndexNum As Integer Application.Volatile Select Case rCell.Interior.ColorIndex Case 1 strColor = "Black" iIndexNum = 1 Case 53 strColor = "Brown" iIndexNum = 53 Case 52 strColor = "Olive Green" iIndexNum = 52 Case 51 strColor = "Dark Green" iIndexNum = 51 Case 49 strColor = "Dark Teal" iIndexNum = 49 Case 11 strColor = "Dark Blue" iIndexNum = 11 Case 55 strColor = "Indigo" iIndexNum = 55 Case 56 strColor = "Gray-80%" iIndexNum = 56 Case 9 strColor = "Dark Red" iIndexNum = 9 Case 46 strColor = "Orange" iIndexNum = 46 Case 12 strColor = "Dark Yellow" iIndexNum = 12 Case 10 strColor = "Green" iIndexNum = 10 Case 14 strColor = "Teal" iIndexNum = 14 Case 5 strColor = "Blue" iIndexNum = 5 Case 47 strColor = "Blue-Gray" iIndexNum = 47 Case 16 strColor = "Gray-50%" iIndexNum = 16 Case 3 strColor = "Red" iIndexNum = 3 Case 45 strColor = "Light Orange" iIndexNum = 45 Case 43 strColor = "Lime" iIndexNum = 43 Case 50 strColor = "Sea Green" iIndexNum = 50 Case 42 strColor = "Aqua" iIndexNum = 42 Case 41 strColor = "Light Blue" iIndexNum = 41 Case 13 strColor = "Violet" iIndexNum = 13 Case 48 strColor = "Gray-40%" iIndexNum = 48 Case 7 strColor = "Pink" iIndexNum = 7 Case 44 strColor = "Gold" iIndexNum = 44 Case 6 strColor = "Yellow" iIndexNum = 6 Case 4 strColor = "Bright Green" iIndexNum = 4 Case 8 strColor = "Turqoise" iIndexNum = 8 Case 33 strColor = "Sky Blue" iIndexNum = 33 Case 54 strColor = "Plum" iIndexNum = 54 Case 15 strColor = "Gray-25%" iIndexNum = 15 Case 38 strColor = "Rose" iIndexNum = 38 Case 40 strColor = "Tan" iIndexNum = 40 Case 36 strColor = "Light Yellow" iIndexNum = 36 Case 35 strColor = "Light Green" iIndexNum = 35 Case 34 strColor = "Light Turquoise" iIndexNum = 34 Case 37 strColor = "Pale Blue" iIndexNum = 37 Case 39 strColor = "Lavendar" iIndexNum = 39 Case 2 strColor = "White" iIndexNum = 2 Case 37 strColor = "Pale Blue" iIndexNum = 37 Case 17 strColor = "Periwinkle" iIndexNum = 17 Case 18 strColor = "Plum" iIndexNum = 18 Case 19 strColor = "Ivory" iIndexNum = 19 Case 20 strColor = "Light Turquoise" iIndexNum = 20 Case 21 strColor = "Dark Purple" iIndexNum = 21 Case 22 strColor = "Coral" iIndexNum = 22 Case 23 strColor = "Ocean Blue" iIndexNum = 23 Case 24 strColor = "Ice Blue" iIndexNum = 24 Case 25 strColor = "Dark Blue" iIndexNum = 23 Case 26 strColor = "Pink" iIndexNum = 26 Case 27 strColor = "Yellow" iIndexNum = 27 Case 28 strColor = "Turquoise" iIndexNum = 28 Case 29 strColor = "Violet" iIndexNum = 29 Case 30 strColor = "Dark Red" iIndexNum = 30 Case 31 strColor = "Teal" iIndexNum = 31 Case 32 strColor = "Blue" iIndexNum = 32 End Select If ColorName = True Then CellColor = strColor Else CellColor = iIndexNum End If End Function Gord Dibben MS Excel MVP On Wed, 16 Sep 2009 15:58:31 -0400, "Rick Rothstein" wrote: I don't think there are "official" names for all the colors, but if you copy/paste this code line into the Immediate window and execute it (you can also but a Sub/EndSub around it and execute it as a macro if you want), the code will fill the first 56 rows with the 56 available color indexes... you can look at them and decide on the names to describe them yourself. For X = 1 To 56: Cells(X, "A").Interior.ColorIndex = X: Next |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Cell Shading Colour Property
Hi
thanks for that - I was hoping the volatile would sort out the issue of the #NAME error.. bw Tim "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Since changing the color in a cell does not trigger calculation the "volatile" is superfluous. The originator of the code used it and I have not bothered to remove. Gord Dibben MS Excel MVP On Wed, 16 Sep 2009 21:34:14 +0100, "Tim Childs" wrote: Hi Gord that's just the ticket! thanks v much. pl can you just explain the volatile at the beginning: I did not have that in my basic function and I do get some problems (e.g. get the #NAME erro initially and have to force recalculation) when copying the function to other workbooks (after moving the module into the other book, of course) finally, why do plain cells have an index of -4142 not a small positive integer? thanks Tim "Gord Dibben" <gorddibbATshawDOTca wrote in message . .. Not official as Rick states but these are close. =CellColor(A1,true) Note: if colors have been modified from default...............all goes out the window<g Function CellColor(rCell As Range, Optional ColorName As Boolean) Dim strColor As String, iIndexNum As Integer Application.Volatile Select Case rCell.Interior.ColorIndex Case 1 strColor = "Black" iIndexNum = 1 Case 53 strColor = "Brown" iIndexNum = 53 Case 52 strColor = "Olive Green" iIndexNum = 52 Case 51 strColor = "Dark Green" iIndexNum = 51 Case 49 strColor = "Dark Teal" iIndexNum = 49 Case 11 strColor = "Dark Blue" iIndexNum = 11 Case 55 strColor = "Indigo" iIndexNum = 55 Case 56 strColor = "Gray-80%" iIndexNum = 56 Case 9 strColor = "Dark Red" iIndexNum = 9 Case 46 strColor = "Orange" iIndexNum = 46 Case 12 strColor = "Dark Yellow" iIndexNum = 12 Case 10 strColor = "Green" iIndexNum = 10 Case 14 strColor = "Teal" iIndexNum = 14 Case 5 strColor = "Blue" iIndexNum = 5 Case 47 strColor = "Blue-Gray" iIndexNum = 47 Case 16 strColor = "Gray-50%" iIndexNum = 16 Case 3 strColor = "Red" iIndexNum = 3 Case 45 strColor = "Light Orange" iIndexNum = 45 Case 43 strColor = "Lime" iIndexNum = 43 Case 50 strColor = "Sea Green" iIndexNum = 50 Case 42 strColor = "Aqua" iIndexNum = 42 Case 41 strColor = "Light Blue" iIndexNum = 41 Case 13 strColor = "Violet" iIndexNum = 13 Case 48 strColor = "Gray-40%" iIndexNum = 48 Case 7 strColor = "Pink" iIndexNum = 7 Case 44 strColor = "Gold" iIndexNum = 44 Case 6 strColor = "Yellow" iIndexNum = 6 Case 4 strColor = "Bright Green" iIndexNum = 4 Case 8 strColor = "Turqoise" iIndexNum = 8 Case 33 strColor = "Sky Blue" iIndexNum = 33 Case 54 strColor = "Plum" iIndexNum = 54 Case 15 strColor = "Gray-25%" iIndexNum = 15 Case 38 strColor = "Rose" iIndexNum = 38 Case 40 strColor = "Tan" iIndexNum = 40 Case 36 strColor = "Light Yellow" iIndexNum = 36 Case 35 strColor = "Light Green" iIndexNum = 35 Case 34 strColor = "Light Turquoise" iIndexNum = 34 Case 37 strColor = "Pale Blue" iIndexNum = 37 Case 39 strColor = "Lavendar" iIndexNum = 39 Case 2 strColor = "White" iIndexNum = 2 Case 37 strColor = "Pale Blue" iIndexNum = 37 Case 17 strColor = "Periwinkle" iIndexNum = 17 Case 18 strColor = "Plum" iIndexNum = 18 Case 19 strColor = "Ivory" iIndexNum = 19 Case 20 strColor = "Light Turquoise" iIndexNum = 20 Case 21 strColor = "Dark Purple" iIndexNum = 21 Case 22 strColor = "Coral" iIndexNum = 22 Case 23 strColor = "Ocean Blue" iIndexNum = 23 Case 24 strColor = "Ice Blue" iIndexNum = 24 Case 25 strColor = "Dark Blue" iIndexNum = 23 Case 26 strColor = "Pink" iIndexNum = 26 Case 27 strColor = "Yellow" iIndexNum = 27 Case 28 strColor = "Turquoise" iIndexNum = 28 Case 29 strColor = "Violet" iIndexNum = 29 Case 30 strColor = "Dark Red" iIndexNum = 30 Case 31 strColor = "Teal" iIndexNum = 31 Case 32 strColor = "Blue" iIndexNum = 32 End Select If ColorName = True Then CellColor = strColor Else CellColor = iIndexNum End If End Function Gord Dibben MS Excel MVP On Wed, 16 Sep 2009 15:58:31 -0400, "Rick Rothstein" wrote: I don't think there are "official" names for all the colors, but if you copy/paste this code line into the Immediate window and execute it (you can also but a Sub/EndSub around it and execute it as a macro if you want), the code will fill the first 56 rows with the 56 available color indexes... you can look at them and decide on the names to describe them yourself. For X = 1 To 56: Cells(X, "A").Interior.ColorIndex = X: Next |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change selected cell shading colour | Excel Discussion (Misc queries) | |||
| How do I change the shading colour when selecting cells? | Excel Discussion (Misc queries) | |||
| How can colour of cell shading be fixed to one colour | Excel Discussion (Misc queries) | |||
| Is there a way to add cells based on their shading / fill colour? | Excel Discussion (Misc queries) | |||
| How do I customise the colour of cell shading? Thank you | Excel Discussion (Misc queries) | |||
Hybrid Mode
