Solved Excel 2007 Conditional cell colouring

June 21, 2012 at 07:19:26
Specs: Windows 7
I have a spreadsheet (Excel 2007) where I am planning an event programme. I have a series of named cells "band1", "band2", "mc1", "mc2" etc.
I then fill up my programme by putting "=band1" or whatever in the appropriate time slot in the programme.

What I want to do is have not only the text associated with that named cell appear but also the cells fill colour. That way if I colour the named cell I can immediately see all the times that that band or MC are active.

I have looked at conditional formatting but cannot see how to do it. I can easily make all cells equal to a named cell have a fixed colour, but not to copy the colour to use from the named cell.

Any ideas gratefully received - if I need to write some code to do it a few basic guidelines would help (I am reasonably familiar with writing code)

Thanks in advance


See More: Excel 2007 Conditional cell colouring

June 22, 2012 at 18:05:34
✔ Best Answer
First off, we can tell when you are lying to us. ;-)

You can't have a cell named "mc1" or "mc2" since those are cell references and can't be used as a Range Name. Go ahead, try defining a name of mc1 and let us know what happens.

Now as far as your project, you should be able to do it with Conditional formatting as follows. (I was.)

Let's say your Named Ranges are A1:A4.

A1 is named band1
A2 is named band2
A3 is named emcee1
A4 is named emcee2

Let's your "Time Slots" are in B1:F100

1 - Eliminate the fill colors from the Named cells (A1:A4)
2 - Select A1:F100
3 - With all those cells selected, enter these Conditional Formatting rules, choosing a different fill color for each rule:

New Rule:
=A1=band1 (Pick a fill color)

New Rule:
=A1=band2 (Pick a fill color)

New Rule:
=A1=emcee1 (Pick a fill color)

New Rule:
=A1=emcee2 (Pick a fill color)

When you are done, all of your Named cells should turn the color you choose for that Name since they equal themselves and the CF formula will be TRUE.

In addition, any cell where the result of a formula matches the value in one of the Named cells, the CF will be TRUE and the formatting will match the formatting of the Named cell that the formula refers to.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

June 28, 2012 at 09:43:36
Thanks for the help.

Yes, of course the cells were not actually named "mc2", "mc2" - when writing the posting I did not bother to check exact names, and of course those would not have worked in practice.

Your principle works, though what I actually wanted was to be able to easily turn the cell colouring on or off so that I could easily highlight say only "band1" and not the others. Originally I hoped to just colour the named cell and have all cells equal to it to be highlighted. However I have managed a next best thing. I created a set of flags in a nearby column and changed your formula to =AND(A1=band1,$H$1="*"), AND(A1=band2,$H$2="*") etc. Now when I put a * in h1 - h4 the appropriate cells are highlighted.

Report •

June 28, 2012 at 11:46:47
I'm glad you came up with a solution that works for you.

To do what you wanted to do without using a flag cell would either require VBA or a conditional formatting rule that included the flag in the same cell:

=A1=band1 & "*"

I think your way is probably the best.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question