formatting cells base on value of other cells

sanlen

New Member
Joined
Aug 24, 2010
Messages
39
Hi,

I am trying to use conditional formatting to formate the cell base on the values of other cells.

I have attached the link to show the sample of the formatting that i am trying to show on my sheet.

http://www.box.net/shared/e08o6su5i3


Your comments will be very much appreciated and sorry for any inconvenience.

Thanks,
sanlen
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
I cannot view that link due to company access restrictions, and suppose that would be true of quite a few people here. Can you describe what you are aiming for?
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
Hi,

No access to view your sheet at work, but maybe this would help:

Format -> Conditional Formatting -> Formula is: =IF($B$1="value",1,0)
 

sanlen

New Member
Joined
Aug 24, 2010
Messages
39
Hi,

I need to show the progress of the task in the map formatting. We have many tasks need to be completed on few different regions. We have 4 different milestones (MS01, MS02, MS03, MS04) to control the progress of the task too. What i am looking for is something like this:

- if MS01 is date then the color of cell sheet1!D5 is YELLOW (taks already started).
- if MS04 is date then the task already completed and color of cell sheet!D5 is BLUE
- if any of MS01 through MS04 contail work "ISSUE" then color of sheet1!D5 is RED.

Those will also be applied to the other cell.

Thanks,
sanlen
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197

ADVERTISEMENT

Where are MS01 - MS04 stored?
 

sanlen

New Member
Joined
Aug 24, 2010
Messages
39
MS01 is at sheet1!J5
MS02 is at sheet1!K5
MS03 is at sheet1!L5
MS04 is at sheet1!M5

Thanks,
sanlen
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197

ADVERTISEMENT

Hi sanlen,

Try:

Format -> Conditional Formatting -> Formula is:

Condition 1. =IF(OR($J$5:$M$5="Issue"),1,0) - Set to RED
Condition 2. =IF(CELL("format",M5)="D1",1,0) - Set to BLUE
condition 3. =IF(CELL("format",J5)="D1",1,0) - Set to YELLOW
 

sanlen

New Member
Joined
Aug 24, 2010
Messages
39
Hi,

Thanks.

But Condition 1. =IF(OR($J$5:$M$5="Issue"),1,0) does not work for me. I dont get any changes in my cell.

Please advice.

Thanks,
sanlen
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
So in the range J5:M5 you have set some values to "Issue" and it isn't working? It works for me!

It may be that J5:M5 are headers (MS01 - MS04), and your values are actually in J6:M6, in which case change the formula accordingly.
 

sanlen

New Member
Joined
Aug 24, 2010
Messages
39
Hi,

It does not work in this way:

The task already started on 7/Mar/2011, so MS01=7/Mar/2011 and the activity of MS01 also already completed then i am ready to start MS02. Unfortunately, some problems occure during the process of MS02 so the value of this MS02 will be "ISSUE".

At this point my cell is still in YELLOW and not change from this YELLOW to RED.

MS01------MS02
7/Mar------ISSUE


Thanks,
sanlen
 

Watch MrExcel Video

Forum statistics

Threads
1,109,040
Messages
5,526,409
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top