If Formula and cell fill


On sheet10 cell c21 trying to color fill the cell, based on if sheet13!AA14 is <.25, then Purple, if between .25 and .5, then Red, if between .5 and .75, orange, .75 to 1, Yellow, else dark green.
Who is Participating?
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:

the solution depends a bit on your version of Excel. In Excel 2010 and later, you can reference a cell on a different sheet in a conditional formatting formula. In earlier versions this does not work and you'll need to create a named range first. The named range approach also works in later versions, so here is how it works.

Select cell AA14 on Sheet13  and create a range name for it, for example TheCell

Then, on Sheet10, select cell C21 and create a new conditional format with a formula (Home ribbon > Conditional Formatting > New rule > use a formula to determine ....)

Create rules and arrange them in this order
=TheCell<0.25  -- purple
=TheCell<=0.5  -- red
=TheCell<=0.75 -- orange
=TheCell<=1 -- yellow

Format the cell green. If none of the conditional formats apply, the default green will show.

cheers, teylyn
Steven HarrisPresidentCommented:
I am assuming you are looking for Conditional Formatting Formulas?

Purple  is =Sheet13!$AA$14<0.25
Red is =Sheet13!$AA$14<=0.5
Orange is =Sheet13!$AA$14<=0.75
Yellow is =Sheet13!$AA$14<=1
Green is =Sheet13!$AA$14>1

Create them in the same order, have them stop if true.
sandramacAuthor Commented:
Hi would this week for a range of cells saw from AA14 to AM14 and then the conditional formatting from cell21 to 34.
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
What version of Excel are you using? Can you upload a sample file with dummy data?

Yes, the principle can be applied to a block of cells, but it needs to be set up correctly. A sample file that represents your data structure would be most helpful.

cheers, teylyn
sandramacAuthor Commented:
Teylyn, Thanks for the information, I figured it out with your guidance.  Thank You
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.