Change a cell color based on the number in the cell

[Higgler]

I'd like to paste a set of data (an array of data) to a worksheet and have it automatically change the color in each cell of the worksheet to show pass fail and close to spec. i.e. a quick color chart.

Hence a large table of measured data in one worksheet can be compared to the spec. worksheet and color the data worksheet from green to yellow to red depending on the measured data.

I could subtract the two worksheets and form a delta worksheet compared to spec and then have the colors change on that worksheet. Either is ok.

I searched this sight for this, but didn't see any.

appreciate any hints.
Thanks.

 

[IRstuff]

Look for "Conditional Formatting" on the Windows 7 home ribbon.

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[zdas04]

Have you tried "Conditional Formatting"? I think it is designed to do exactly what you described.

David

 

[Higgler]

Thanks, that works correctly, but in using a formula (data-spec), it seems that I need to do that individually for every single cell that I want the data set to show the color.

I know that I can copy formatting from one cell to the next, but I'd like the formula for conditional formatting to increment from cell to cell. i.e. if I do conditional formatting on one cell as;
=(data A1-specA100)>0 (format color background of cell A1 to green)

then I'd like to copy and paste it so that the pasted formatting for data in cell Z1 compared to spec in cell Z100 becomes =(dataZ1-spec Z100)>0 (format color background of A1 to green).

Also, seems I can only do one color at a time, i.e. pick a green for passing or red for failing for the whole data set. i.e. can't have two colors in the resulting formatting.

At least not yet anyhow.

thanks for the hint, helped.

 

[Higgler]

found it, thanks for the tips.
This formatting changes alot from 2003 to 2007 to 2010 Excel, at least the view of the menu's change alot.