working out which colour text is used to populate a cell

[Matt]

Hi all,

Fingers crossed someone out there will be able to help me.
I have been tasked to provide a spreadsheet at work to monitor a number of
applications and written applications that we receive and the number of days
we respond by.

I have the following cells ‘application received’(Cell B), ‘enquiry
received’(Cell C), ‘date completed’(Cell E) I then have the following formula
in Cell H =NETWORKDAYS(IF(B4<>"",B4,C4),E4,$A$119:$A$158) which determines
which cell has the date in (B or C) and then works out the number of days it
has taken looking at cell E (the data within the $ refers to national
holidays listed on the spreadsheet).

Then using conditional formatting in Cell H I use the following formula
=AND(B4="",C4>0) so that if it’s the application date (Cell B) is used the
text remains black and if its enquiry date (Cell C) used the answer is given
in red so that you can differentiate between the two.

I then need to work out if the application or enquiry was dealt within
10days or 11days+ and I have the following cells ‘application number of days
<10’ (Cell I), ‘application Number of days 11+’ (Cell J), ‘written enquiries
<10’ (Cell K), ‘written enquiries 11+’ (Cell L).

I was thinking of then using =IF(H4<=10,H4,"") to populate the answer in
Cell I if within 10days and =IF($H4>=11,$H4,"") if the number of days was
11+. But I need a simple way (if there is one) for the spreadsheet to look at
Cell H determine if the answer is written in black or red text, if it’s in
black text it will put the number in either <10(Cell I) or 11+ (Cell J) and
if its red text put the number in <10(Cell K) or 11+ (Cell L).

Can someone please help me on this (I hope I’ve explained it well)

Thanking you all in advance

 

[joel]

You need to read Chip Pearson's website.

'Conditional Formatting
(http://www.cpearson.com/Excel/cformatting.htm)


You wil need to read the links on the page as well especially the on
which titled : Determining Which Format Condition Is In Effect