Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting all cells of a certain fill (interior) colour (macro?)
I work in Learning Support, and a lot of my students like to have a coloured
background on worksheets, to alleviate tracking difficulties when reading the text etc... However, although anything other than white is better (e.g. cream / off white)they tend to have their own personal colour which is best for them. What I'd like to be able to do is to quickly change the colour of cells to suit their individual preferences. However, I often use other colours within my worksheets to indicate "notes" cells, table headers, input cells, output cells, etc... etc... If I select the whole sheet to colour, so that every cell gets a new interior (fill) colour, this obviously affects those cells that I want to stay the same as well. So I then have to re-select the 30-odd input cells, colour those back to their original colour, then the output cells etc... This can take far too long to make it worthwhile. Is there a way to select all of just one particular type of cell (e.g. all the "background" cells) and not the other coloured cells that I want to stay the same? I've tried naming the input cells etc... and using "Go To", but it would seem that it can only refer to a certain number of different ranges, and if the cells are spread non-adjacently through the sheet, one name is not enough to capture all of them. Ideally I'd like a macro or similar so that I can specify which interior colour index to pick out, and select all of the cells in the worksheet that meet that criteria, so that I can then format those cells only, leaving the others untouched. Is there some sort of code to do this? Even better, does anybody know of a way such that on pressing a button, say, a dialog box could pop up to ask me what interior colour to look for (which I would enter), then ask me what colour I want to change these cells to (which I would enter again), and then select them all and make the change for me? If this was possible, I could really do a good job in differentiating for the students without it taking me forever to do so. Any help would be really appreciated. Many thanks in advance, Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting all cells of a certain fill (interior) colour (macro?)
Hi Neil,
You could call John Walkenbach's Color Piker Dialogbox from a button routine: http://www.j-walk.com/ss/excel/tips/tip49.htm The button code could apply the chosen colour and then reset the colour of predefined coloured ranges. There should be no intrinsic problem in assigning names to the coloued ranges: simply select each range manually, then assign a name using the sheets's name box. --- Regards, Norman "Neil Goldwasser" wrote in message ... I work in Learning Support, and a lot of my students like to have a coloured background on worksheets, to alleviate tracking difficulties when reading the text etc... However, although anything other than white is better (e.g. cream / off white)they tend to have their own personal colour which is best for them. What I'd like to be able to do is to quickly change the colour of cells to suit their individual preferences. However, I often use other colours within my worksheets to indicate "notes" cells, table headers, input cells, output cells, etc... etc... If I select the whole sheet to colour, so that every cell gets a new interior (fill) colour, this obviously affects those cells that I want to stay the same as well. So I then have to re-select the 30-odd input cells, colour those back to their original colour, then the output cells etc... This can take far too long to make it worthwhile. Is there a way to select all of just one particular type of cell (e.g. all the "background" cells) and not the other coloured cells that I want to stay the same? I've tried naming the input cells etc... and using "Go To", but it would seem that it can only refer to a certain number of different ranges, and if the cells are spread non-adjacently through the sheet, one name is not enough to capture all of them. Ideally I'd like a macro or similar so that I can specify which interior colour index to pick out, and select all of the cells in the worksheet that meet that criteria, so that I can then format those cells only, leaving the others untouched. Is there some sort of code to do this? Even better, does anybody know of a way such that on pressing a button, say, a dialog box could pop up to ask me what interior colour to look for (which I would enter), then ask me what colour I want to change these cells to (which I would enter again), and then select them all and make the change for me? If this was possible, I could really do a good job in differentiating for the students without it taking me forever to do so. Any help would be really appreciated. Many thanks in advance, Neil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting all cells of a certain fill (interior) colour (macro
Thanks for your reply Norman. Unfortunately though, naming my "input cells"
still doesn't quite work. Maybe it is just this particular worksheet (would it dislike merged cells perhaps?). If I select the 30-odd cells I want to name under one group (e.g. naming it Input_Cells), click off these cells, choose Go To Input_Cells,.... it only remebers some of those 30-odd cells but not all of them - very frustrating! I've had another go just now, but no joy. Any thoughts on why it's playing me up? "Norman Jones" wrote: Hi Neil, You could call John Walkenbach's Color Piker Dialogbox from a button routine: http://www.j-walk.com/ss/excel/tips/tip49.htm The button code could apply the chosen colour and then reset the colour of predefined coloured ranges. There should be no intrinsic problem in assigning names to the coloued ranges: simply select each range manually, then assign a name using the sheets's name box. --- Regards, Norman "Neil Goldwasser" wrote in message ... I work in Learning Support, and a lot of my students like to have a coloured background on worksheets, to alleviate tracking difficulties when reading the text etc... However, although anything other than white is better (e.g. cream / off white)they tend to have their own personal colour which is best for them. What I'd like to be able to do is to quickly change the colour of cells to suit their individual preferences. However, I often use other colours within my worksheets to indicate "notes" cells, table headers, input cells, output cells, etc... etc... If I select the whole sheet to colour, so that every cell gets a new interior (fill) colour, this obviously affects those cells that I want to stay the same as well. So I then have to re-select the 30-odd input cells, colour those back to their original colour, then the output cells etc... This can take far too long to make it worthwhile. Is there a way to select all of just one particular type of cell (e.g. all the "background" cells) and not the other coloured cells that I want to stay the same? I've tried naming the input cells etc... and using "Go To", but it would seem that it can only refer to a certain number of different ranges, and if the cells are spread non-adjacently through the sheet, one name is not enough to capture all of them. Ideally I'd like a macro or similar so that I can specify which interior colour index to pick out, and select all of the cells in the worksheet that meet that criteria, so that I can then format those cells only, leaving the others untouched. Is there some sort of code to do this? Even better, does anybody know of a way such that on pressing a button, say, a dialog box could pop up to ask me what interior colour to look for (which I would enter), then ask me what colour I want to change these cells to (which I would enter again), and then select them all and make the change for me? If this was possible, I could really do a good job in differentiating for the students without it taking me forever to do so. Any help would be really appreciated. Many thanks in advance, Neil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting all cells of a certain fill (interior) colour (macro?)
Hi Neil,
Your overall objective can be achieved simply by changing the "Normal" style. Format Style... ensure "Normal" is highlit Modify... Patterns After setting a colour for your normal fill-format you will loose gridlines, assuming you use them. Borders. Apply a thin border to all edges. Also select (say) light grey and click on each border to apply the border colour. If you're not satisfied with the choice of default palette colours, customise one Tools Options Color Suggest customize one or more of the chart colours in the bottom two rows and to the right of the palette. Make a note of the RGB values of some potential colours. All this, changing the normal style and customizing colours can be done with simple macros. The Normal style and customized palette is saved with the workbook Regards, Peter T "Neil Goldwasser" wrote in message ... I work in Learning Support, and a lot of my students like to have a coloured background on worksheets, to alleviate tracking difficulties when reading the text etc... However, although anything other than white is better (e.g. cream / off white)they tend to have their own personal colour which is best for them. What I'd like to be able to do is to quickly change the colour of cells to suit their individual preferences. However, I often use other colours within my worksheets to indicate "notes" cells, table headers, input cells, output cells, etc... etc... If I select the whole sheet to colour, so that every cell gets a new interior (fill) colour, this obviously affects those cells that I want to stay the same as well. So I then have to re-select the 30-odd input cells, colour those back to their original colour, then the output cells etc... This can take far too long to make it worthwhile. Is there a way to select all of just one particular type of cell (e.g. all the "background" cells) and not the other coloured cells that I want to stay the same? I've tried naming the input cells etc... and using "Go To", but it would seem that it can only refer to a certain number of different ranges, and if the cells are spread non-adjacently through the sheet, one name is not enough to capture all of them. Ideally I'd like a macro or similar so that I can specify which interior colour index to pick out, and select all of the cells in the worksheet that meet that criteria, so that I can then format those cells only, leaving the others untouched. Is there some sort of code to do this? Even better, does anybody know of a way such that on pressing a button, say, a dialog box could pop up to ask me what interior colour to look for (which I would enter), then ask me what colour I want to change these cells to (which I would enter again), and then select them all and make the change for me? If this was possible, I could really do a good job in differentiating for the students without it taking me forever to do so. Any help would be really appreciated. Many thanks in advance, Neil |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting all cells of a certain fill (interior) colour (macro
Hi Neil,
For the purpose of providing the background colour, whilst retaining the pre-determined range colours, Peter's suggestion is a good one. My reference to John Walkenbach's Color Picker was based on the (possibly inappropriate) assumption that each student would set and change the background colour arrangement. If, conversely, this operation is to be performed by you, or a colleague, the Colour Picker would be unnecessary. --- Regards, Norman "Neil Goldwasser" wrote in message ... Thanks for your reply Norman. Unfortunately though, naming my "input cells" still doesn't quite work. Maybe it is just this particular worksheet (would it dislike merged cells perhaps?). If I select the 30-odd cells I want to name under one group (e.g. naming it Input_Cells), click off these cells, choose Go To Input_Cells,.... it only remebers some of those 30-odd cells but not all of them - very frustrating! I've had another go just now, but no joy. Any thoughts on why it's playing me up? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the shading colour when selecting cells? | Excel Discussion (Misc queries) | |||
Function to return interior colour of a cell | Excel Programming | |||
Sum cells by fill colour | Excel Discussion (Misc queries) | |||
Sum cells according to colour fill | Excel Discussion (Misc queries) | |||
Code to change interior colour only if current interior colour is | Excel Programming |