• 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.

Conditional Formatting based on different cells color coding [SOLVED]

S

sranyc

New Member
I have an excel data in 10ten columns. Each column is defined with distinct conditional formatting respectively with Green, Red & Yellow colors. Now based on below mentioned rule i want 11th column to be color coded automatically.


Green - If all the columns are green

Yellow - If any one column is yellow

Red - If any one column is red


Thanks in advance.
 
Hi ,


I am not sure that you can look at a cell's CF color using formulae ; what you can do is to write down the conditions for each column to be GREEN , and use an AND of all these conditions for making the 11th column GREEN.


Similarly , take an OR of the conditions for RED , YELLOW for the 10 columns to decide whether the 11th column should be RED , YELLOW.


Narayan
 
You nay want to have a read of:

http://chandoo.org/forums/topic/how-to-identify-background-color-of-a-cell-with-a-formula
 
I have one suggestion - instead of write down all the variety of the conditions in 10 columns (seems each column uses different CF), add 10 more columns (say AA, AB, AC, etc.). Manually assign values of 1,2, or 3 to column AA if the first column is green, yellow or red. Repeat it for all columns until all have the values assigned, then go to the 11th column and use AND/OR formula, as Narayank991 suggested above, to decide what should be the value in column 11 and CF 11th column to green, yellow or red.


Even it is manual, I feel it will be quicker than writing all the conditions in the AND/OR formula. Just a thought.
 
Hi first of all thanks to narayan, hui (excel ninja) & nelsonwang.

But each cell in my excel is having different validation and having 35 columns and 100 rows in a single work sheet, which is not possible to give manually....
 
Sranyc


You can use the UDF I suggested above to read the colors,

What you do with the colors is up to you, but they can be used in CF's


Can you post a sample file to simplify it for us?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi,


I tried using UDF provided by you but unable to acheive the target. and am facing pblm in uploading file also...
 
Sranyc


My apologies, I have misread your original post


Yes, You have used Conditional Formats and those UDF's don't work on CF colors.


I'd suggest adding spare columns as Nelson Wang suggested above

Effectively you'll have a set of columns BA:CW replicating A:AW with simple values in each cell replicating what the CF are doing


Then you can base a Overall Result on the values in cells BA:CW


Is it ok for me to post your file for others to review?
 
Hi,


Thanks for the suggestion. Actually i could not understand what nelson Wang said. As already i shared the sample template with you, can you please guide me in doing the same for the first entry(proj1) so that i can do it for the remaining.


Thanks in advance.
 
I would setup Cells BA2:CW2 to replicate the CF of A2:AW2

I would assign values for each cell

eg 1 = Green, 2 = Yellow, 3 = Red


Then in each cell eg

in BC2 use a formula =If(C2<=30,1,If(C2<45,2,3))

in BS2 =If(S2="Yes",1,3)

etc


Then in AX2 use a Formula =max(BA2:CA2)

It will return 1 if there are only blanks or Green cells

It will return 2 if there are any Yellow cells

It will return 3 if there are any Red cells


ps: If you don't understand something, ASK!
 
Hi Hui,


Thanks for the extended help . The suggestion given by you is working.


in addition to the same i need some more help, like can i hide & lock the replicated cells like A2:AW2.


And in AX2 cell can i replace the values of 1,2 &3 with the color names like green, yellow red.


Thanks in advance.
 
Hi Hui, I extention to my last query,


And in AX2 cell can i replace the values of 1,2 &3 with the color names like green, yellow red. - this particular thing is solved.


now i want to know only about below mentioned query...


like can i hide & lock the replicated cells like A2:AW2.
 
Thanks to one and all who supported me to solve my query. Now my logic is working fine.


Thanks to everyone


This is solved
 
You must log in or register to reply here.
Back
Top