Changing Cell colours

Good morning johncon

...and welcome to the forum!!

What version of Excel do you have?

DominicB

Excel 2000 has no colour functions. You could write a User Defined Function to get the ColorIndex of a cell. You would then ned to use Conditional formattig to colour the cell based n the result because a function cannot affect a cell.

Hi johncon

OK - it can be done but you're breaking a few of Excels rules here, so it's not a straightforward question.

Using conditional formatting you can't use it across worksheets, and Excel2000 doesn't have a function to detect cell colours (not invented till Excel 2007) so we'll have to put our own in. First we'll tackle the cell colours bit - either use the one from here under Return the ColorIndex of a Cell:
http://www.cpearson.com/excel/colors.htm
or you can download my add-in from the link below, and once installed go to Ultimate > Formulae > Add Function, select Cell Colour from the list and click Add Function. This will add the necessary code to your workbook.

Now the cell that you want to test for a colour (let's say cell A1 on Sheet1) needs to be set up as a named range. So select the cell, go to Insert > Name > Define, type a name into the box (I've used "myname") and click Add and OK.

In a spare cell on Sheet1 type the formula:
=CellColour(A1)
This formula will be different if you used Chip Pearson's function.
This will give you the ColorIndex of the cell - I've used loud yellow - ColorIndex 6.
Now go to your other sheet (Sheet2) and in the cell that has to reflect the colour go to Format > Conditional Formatting, select Formula Is and put this formula in the box :
=CellColour(myname)=6
and select your format.

Phew! It's a bit of a trek, but it does work.

HTH

DominicB

Her's a quick example. See the formula 7 Conditional Formatting of E3 in Sheet1 7 G3 in Sheet2

You didn't say that you would have more than one cell or colour! You can adapt the suggestions with VBA but how many conditions would you have?