How to use conditional format on another cellClosed

Question

I want to format a cell using 2 conditions from a another variable cell.

Example 
The cell i want to format is A1 using D1.
when D1 is between 0.5 and 0.75 i want AI to be coloured as yellow.
When D1 is between 0.75 and 2 i want A1 to be coloured as red.
If the 2 conditions are not met I want A1 to remain as green.

Please help how can i do this?

Mazzaropi · Answer

bbdwells, Good morning. You can use three rules to obtain your differents colored cells. Select A1. Menu Conditional Formatting. Using Formulas. Rule 1 : =AND(D1>=0.5,D1<=0.75) .............Choose YELLOW as fill cell. Rule 2 : =AND(D1>0.75,D1<=2) .............Choose RED as fill cell. Rule 3 : =AND(D1<0.5,D1>2) .............Choose GREEN as fill cell. IF your cells are coloured as GREEN by default, you don't need to create a 3rd rule. Is it what you want? I hope it helps. -- Belo Horizonte, Brasil. Marcílio Lobão

bbdwells · Answer

Thank you Marcilio,

the formula seems to work for the cell. I tried to do the same under conditional formatting so that the green, red and yellow icons can be displayed on A1 but it did not work.
Thank you for the formula, it is certainly a first lucky step.

BB

Mazzaropi · Answer

bbdwells, Good evening.

Please, take a look at this example I did for you:
http://speedy.sh/RTpgH/10-08-2014-enKioskea-Conditional-Format-bbdwells-OK.xlsx 
 
Is it what you want?
I hope it helps.

Mazzaropi · Answer

bbdwells, Good morning.

Thank you for the feedback.

Please, save your file at a free site, www.speedyshare.com and put the link to download it here.

This way all users here can follow the development and solution of your question.
This is a collaborative place.

I waiting for your link.

Have a nice day. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

bbdwells · Answer

Marcilio,

The link to the document is 

http://www.speedyshare.com/Fzf62/2014-08-11-NEW-LIVE-PROJECT.xlsx

For example, on the (display page) I3 is Mechanical Engineering the value changes daily. 28 is the number of days to do the task and 14 is the number of days till today. =(E3&"/ "&D3) has been used in {I3} at the mechanical engineering page so that excel does not reduce 14/28 to 1/2.

The value in I3 (display page) comes from K3 on the (mechanical engineering page).  (Display page) AF3 is created from M3 on (mechanical Engineering page)

Now, 14/28 is 0.5 as the value changes daily, i want to format the cell I3 (at the display  page) to automatically change color, and show icon set based on the value displayed in AF3 (display page).

If possible i will like to configure excel to automatically send emails to notify the project team when the color changes.

I hope you understand my somewhat complex sheet.

Thank you.

How to use conditional format on another cell

5 responses

Similar discussions

Subscribe To Our Newsletter!