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

Format the numbers to shade and color code

Jack-P-Winner

Jack-P-Winner

Member
When I search for these numbers is it possible to make the even numbers shade in gray and green font and the odd shade in yellow and red font like the attached file. I have manually color coded how I want it to work

H through U are hidden with the code to run the file. I am counting how many gray cells there are and yellow cells
 

Attachments

  • Color code side numbers.xlsx
    40.9 KB · Views: 8
Hi Jack,

Please check the columns from X to AA in the file that I have attached. Is this what you are looking for?

Regards!!!
 

Attachments

  • Color code side numbers-Updated.xlsx
    44.3 KB · Views: 4
AIM said:
Hi Jack,

Please check the columns from X to AA in the file that I have attached. Is this what you are looking for?

Regards!!!
Click to expand...

Aim,

You basically copied what I already have so I am confused. I am wanting column U numbers to be green/gray and yellow/red automatically
 
Hi Larry ,

Nothing is clear.

Do you want the values in column W to be arrived at through a formula , or do you want the values in column W to be conditionally formatted ? Please explain in detail.

Narayan
 
I really do not care how it is done. Im already counting the cells vertically. I "only" want to change the color of the numbers to match the color of the number it counts. Example: row C12: to C:23 should have a red 12 in W. Row E8 to E:9 should have a green 2 in W. Let's not make this more complex than this. Its simple what I want
 
Hi Larry ,

I think we have been over this as many times as you have posted !

What is simple to you may not be so simple to others.

I can understand the following :

The 1 in W46 is because of the one 1 in D46

I do not know how you have arrived at the 0 in W45

The 3 in W44 is because of the string of three 1s in column C , in cells C46 , C45 and C44

I do not know how you have arrived at the 3 in W43

The 5 in W42 is because of the string of five 0s in column E , in cells E46 , E45 , E44 , E43 and E42.

How do we make Excel understand , that is the question ; the answer will not be as simple as you think.

Narayan
 
NARAYANK991 said:
Hi Larry ,

I think we have been over this as many times as you have posted !

What is simple to you may not be so simple to others.

I can understand the following :

The 1 in W46 is because of the one 1 in D46 (no, the 1 is because there is ONE yellow cell)

I do not know how you have arrived at the 0 in W45 (ignore any cells with a 0)

The 3 in W44 is because of the string of three 1s in column C , in cells C46 , C45 and C44 (correct)

I do not know how you have arrived at the 3 in W43 ( the 3 2's) you have to count the yellow and gray cells in a vertical row not the numbers inside them unless they are 1's and 9's

The 5 in W42 is because of the string of five 0s in column E , in cells E46 , E45 , E44 , E43 and E42. (correct)

How do we make Excel understand , that is the question ; the answer will not be as simple as you think.

Narayan
Click to expand...
 
Last edited:
Hi Larry ,

If we go by your explanation , how do you arrive at the 2s in rows 19 and 18 ? I would have thought they should be 0s.

Narayan
 
Hi Larry ,

OK. This is going to take some time , since all these details have emerged after repeated prodding !

Narayan
 
Hi Larry ,

See your file now ; I have put in the formula in column X to derive the actual values , with the formulae in columns Y , Z , AA and AB as the helper columns.

The formula in column AC is a helper for the conditional formatting ; the same formula could be used as the CF formula once you confirm that this works correctly.

Narayan
 

Attachments

  • Nar Color code side numbers.xlsx
    44.6 KB · Views: 15
NARAYANK991 said:
Hi Larry ,

See your file now ; I have put in the formula in column X to derive the actual values , with the formulae in columns Y , Z , AA and AB as the helper columns.

The formula in column AC is a helper for the conditional formatting ; the same formula could be used as the CF formula once you confirm that this works correctly.

Narayan
Click to expand...
Narayan,

What can I say besides YOUR A GENIUS!!!!!!

THANK YOU!!!!!!!

Exactly what I was looking for
 
I am trying to copy the results to another page and I looks the color coding ... any ideas? I have tried opeing my files in the same windows too
 
The file works PERFECT! THANK YOU!!!!

After I get the results(run the code)I go to copy the data in column W with the red and green numbers to a new sheet the colors do not stay red and green, they all become1 color of red. I am using office 2010. I am going to try and copy and paste with 2013 to see if it makes a difference
 
Hi Larry ,

As I have explained in an earlier post , copying one column of formulae will not work.

You need to copy the helper columns too.

If you want , send me your working file where you wish to copy this , and I can do it for you.

Narayan
 
I have 5 books that have 30 sheets each (100 sheets) that I want to use this as a tool to copy the sheets. I will copy each sheet to this file, run it and then wanted to copy the data back to my master book.

Is there a way to copy them with them colors so they will paste with the colors to another sheet? Even if I copy X:AB they all copy to a green font
 
Hi Larry ,

The final formula in column X depends on the intermediate formulae in columns Y through AB.

However , the conditional formatting of the cells in column X ( this is independent of the formulae in this column ) , depends on the intermediate formulae in column AC.

If you wish to transfer the result to another workbook , you will need to copy all the formulae in columns X through AC.

If you send me one workbook , I can do it in one worksheet , and you can then duplicate the same in all the other worksheets and workbooks.

Narayan
 
You must log in or register to reply here.
Back
Top