If statement from a cell colour

[Kevpat]

Hi all,

I'm new to the forum, so be gentle!!!!!!!! I'm sure this question has been asked before, but I can't find it, so thought this was the quickest way...

I have a fairly simple spreadsheet that is around 40 columns wide by 350 rows deep. Each column ascertains to a specific machine in the factory I work and each row to a day of the year. Each machine can have a specific type of breakdown or stoppage so I have specific colours within the cells to identify the type of breakdown (also with a number value detailing the number of cycles of the machine prior to the stoppage).

What I want to do, is write an "IF" statement on a separate sheet to interrogate this data sheet so that if a specific cell is Yellow for example, it'll tell me that on this day on this machine, it has had the type of breakdown that the yellow cell colour refers to. The purpose is to easily see how many breakdowns / stoppages, are occurring on each machine.

Is there anyway I can write an "IF" statement to identify the background colour of a cell and return a text value if the colour is a certain colour?

Many thanks in advance...

Kev.

[zbor]

Since you use 2003 you'll need macro for this.

however, if your colors are associated with numbers then you can create formula over numbers (not colors).

but for this we'll need example workbook.

[Domski]

Hi,

Are the cells coloured manually or by conditional formatting?

Dom

[Kevpat]

Hi, thanks for the speedy replies...

Dom, the cells are coloured manually, not with conditional formatting.

zbor, I'll get a copy of the file added in to have a look at... I actually use 2010, but the people who use the spreadsheet are still on 2003 so the file needs to still be in 2003 so they can use it.

[Kevpat]

As requested, the file in question...

[Kevpat]

Hi there again...

I was wondering if anyone else could shed any light on this problem... It's a sheet I'm using that hopefully will prioritise remedial work in the factory I work in to help us focus on improving breakdowns!!!!

Any suggestions would be greatly appreciated!!!!!

[Kevpat]

Hi again...

I'm sure someone out there will have an answer or solution to this one... I'm starting to pull my hair out, and I ain't got much of that left either?!!!?!??!!!

Any help is greatly appreciated...

Kev.

[Domski]

I'm not able to download your attachment for some reason. Out firewall keeps blocking it.

My advice would be to avoid trying to write formula based on the colour that you have formatted a cell. It would involve using a vba user defined function and those that are based on the formatting of cells do not calculate unless you manually force a full sheet recalc.

Would it not be possible to make entries in the cells that relate to the possible events and conditionally format the cells based on that for appearance? You will find it much easier to base any calculations or formula on actual data in a cell than on the formatting of it.

Dom

[Kevpat]

Hi Domski,

Unfortunatley the guys who use the sheet can't really change what they enter into the sheet so the only way I thought of doing what they need is via the formatting of the cell.

Thanks anyway for taking the time to reply, I guess I'll have to re-think how to go about it.