Selecting all cells of a certain fill (interior) colour (macro?)

[Guest]

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

 

[Norman Jones]

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.

 

[Guest]

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?

 

[Peter T]

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

 

[Norman Jones]

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.