Colour Fill

[Maggie]

Hi

How can I type a value in one cell in a column and automatically colour fill
cells in a range of columns on the same row when this change takes place,
there needs to be an if statement somewhere I think because if the cell = a
all else is blue, if cell = b all else is red etc.

 

[Mike H]

You could use conditional formatting:-

Select as many cells as you want in row 1 from column B. Then
Format|Conditional Format
Select formula is and enter this formula:-
=$A1="A"
Select a cloour.
Click Add and add additional formula
=$A1="B" and select another colour
Click OK
Enter A or B in A1 to change the colour of the row.

Mike

 

[David Biddulph]

Format/ Conditional Formatting/ Formula Is:
continue as appropriate (with suitable choices of absolute and relative
addressing) ...

 

[Maggie]

Hi Guys
Thanks for this it works well. Now how can I copy it from one rown to the
next (dragging does not work)

 

[Maggie]

Also is it possible to increase the number of conditional formats on any one
cell from 3

 

[Pete_UK]

Hi Maggie,

highlight the cells that you currently have this applied to on row 1
and double-click the Format Painter icon - then you can just click on
the first cell of the next row you want this to apply to, then the
next row etc - press <Esc> when you are done.

Hope this helps.

Pete

 

[Pete_UK]

This is the limit that applies in Excel 2003 and earlier - you can
have more conditions per cell in Excel 2007.

You will need a macro if you want to increase the limit in earlier
versions - Bob Phillips has an add-in, CFPlus, which you can download
from here:

http://www.xldynamic.com/source/xld.CFPlus.Download.html

This allows up to 30 conditions.

Hope this helps.

Pete

 

[Maggie]

Hi Pete
Thanks for this link, unfortunately it does not work, it does create the add
in in excel toolbar but then does not work from there. There is more than
one download - do you know which I should use?

 

[Pete_UK]

I haven't used it, so I can't advise further.

You could leave a message on the website - I know that Bob Phillips is
currently involved in the Excel Users Conference, so he won't be able
to get back to you until Saturday.

Hope this helps.

Pete

 

[Gord Dibben]

Did you "launch cfplus" from the xldTools menu item?


Gord Dibben MS Excel MVP