Auto Fill cell with Text Based on Colour of Cell Excel 2007

**fmluder93** · 05-06-2010, 01:43 AM

Im currently putting together a spreadsheet that needs to track results of audits. I have set up conditional formatting that will highlight cell F a certain colour based on the results from cell E (for example if the score is between 80 and 100 cell F will change to green, if the score is between 50 and 79 it will turn amber, etc).

I then want to be able to put text representing the colour in the same cell and have no idea how to do this, or if this can be done. So when Cell E has 85 in it, i want cell F to highlight green and have the text "green" in there, as i want to be able to do counts and graphs based on this data.

I have attached a sample of what i mean.

**DonkeyOte** · 05-06-2010, 03:09 AM

You would not do this via Conditional Formatting - rather you would do this via formulae within the cells themselves - replicate the CF logic in the function, eg:

F2: =IF(ISNUMBER($E2),LOOKUP(MAX(0,$E2),{0,50,80,101},{"Red","Amber","Green",""}),"")
copied down

Auto Fill cell with Text Based on Colour of Cell Excel 2007

LinkBack

Thread Tools

Rate This Thread

Display

Auto Fill cell with Text Based on Colour of Cell Excel 2007

Re: Auto Fill cell with Text Based on Colour of Cell Excel 2007

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions