• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Colour shading cells

Simon Lee

Simon Lee

New Member
Hi Everyone

I need the formula to achieve the following:

Cell A1 must turn RED if 80% of cells from A2 to A11 are also shaded red.

(ie.Cell A1 must stay white if less than 80% of A2 to A11 are shaded red.)

Try as i might I can't figure this one out.
 
Hey Simon,

I think there is no formula way to determine the color code on a given cell, we will have to use same criteria using which we are highlighting cells A2 to A11 in red color and then determine the color code to be applied for A1 using similar criteria.


Thanks,
Ramesh
 
How about another way that I can achieve the same aim.

I need to place in to a formula this: If the text in my active Cell which is Cell A2 is exactly the same as the text that is typed into Cell A1, then Cell A2 will shade RED.
 
In such situation, you can take the help of a helper column where if the criteria is met, you will update the value as 1 else 0 and then based on the Percentage of 1's in this column, we can format cell A1
 
For this you can use condition formatting- please refer screen shot below

upload_2016-8-27_13-34-7.png

Simon Lee said:
How about another way that I can achieve the same aim.

I need to place in to a formula this: If the text in my active Cell which is Cell A2 is exactly the same as the text that is typed into Cell A1, then Cell A2 will shade RED.
Click to expand...
 
xlstime thanks for that...perfect....lastly how do I apply the conditional formatting to a whole column of active cells (all sequential) in one go ? IE not only a2 but a3 a4 a5 a6 etc? Currently if i copy paste formatting then the Cell A1 changes to A2, A3 etc
 
Hi Everyone

Maybe this explains it better:

I need to place into a formula this: If the text in any of my active Cells which are Cell A2 all the way to Cell A4000 is exactly the same as the text that is typed into Cell A1, then the Active Cell that have the same text will shade RED.

I can do this for an individual active cell through conditional formatting no problem using the formula =$A$1=$A$2 but as soon as I do =$A$1=$A$2:$A$4000 it doesn't work.

Please help, what am I missing in this formula?
 
Select entire range (A2:A4000). Hit CF and apply formula.
Since formula needs to adjust row, you remove absolute reference from it.
So the formula would be:
=$A$1=$A2
 
You must log in or register to reply here.
Back
Top