Previous Topic Next Topic

Changing color of a cell in a table...

Solved/Closed
ShootingFish - Feb 21, 2010 at 10:02 AM
 ShootingFish - Feb 28, 2010 at 09:13 AM
Hello,

I'm afraid that this post rather fits into the "free help" section of things here,

I am a person with a basic knowledge of coding and i've encountered a problem which i simply cannot figure out for myself,

I thought i would post on here because i think that any solution to my problem can be very useful for other people, as well as myself, who use excel. I'm sure i'm not the first person who has found a need for this sort of thing,

Effectively i am working on a way to make excel do the tedious parts of my job for me. I regurlary have to change the colors of cells in a table in order to denote certain things. Such as "dispatch needed" or "shortage of supply" et al.

This information can be gathered from a seperate spreadsheet into which the relevant data is input using letter codes, an "s" would denote a supply shortage and a "d" would be a dispatch needed. The information is listed by date down column c and the product identification is presented along row 2 of the spread sheet.

In another spreadsheet, the product ID's are all listed together by customer with each column (starting from B) being a customer, and then the ID's running down that column. Currently i have to manually change the color of each cell, based on the issue affecting that product, in order to produce a report on supply for my company.

I would dearly like to have a macro which would look for the date i am working to (which i can specify in a cell on the second worksheet) and then fill the product ID cells with the correct color, based upon the letter code that is input in the first worksheet.

I know very well that this will be possible but as yet, i have no idea on how to make it work. Anybody who makes a suggestion would have my thanks. I am willing to give more details if needed, although i have tried to keep all the little details (like exact cell locations) out of this post, hopefully the VBA code suggestion i recieve will reference not the cells, but the information that would be needed. By this i mean that any cell references would be missed from the code suggestion, and in their place, something like "=[the cell with the date to look up in it]" would be marked in red text, that way a user can input their own cell references and customise the code slightly to suit their own tables.


I look forward to hearing from some of the considerably more talented coders than myself, or anybody who can say something useful about this issue.


ShootingFish
Related:

4 responses

Answer 1 / 4
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 21, 2010 at 10:08 AM
Could you possibly put your file at some share site like https://authentification.site
0
ShootingFish
Feb 21, 2010 at 10:36 AM
https://authentification.site/files/21058029/Example_Sheets.xls

I have hosted an example of what i work with. it's an Office 2000 creation but I will be using office 2003 the the finished article.

What i would like to do is have the macro search for the date mentioned in sheet 2, D4 and then apply colours to the product ID's in the table, the product ID's are named in sheet1 so i imagine that the code would look at that name and then match it up with the identical name in the table, sheet2.

I have decided to focus on just one criteria for the macro, the "D" value. This is because i imagine any code would simply be repeated for any other value, and also because it is by far the one that i use the most (for obvious reasons).

I must mention that the product codes in real life are different and much more numerous, as are the customers (you can imagine a need not to reveal any sensitive information)

Thank you for a quick response! I was suprised to say the least
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > ShootingFish
Feb 22, 2010 at 09:30 AM
Trying to limit my self to formula, this is what needs to be done. I am sure there are other ways too. I added my solution. The url can be located at the end of this answer

First to recap what you had.
1. Two sheets (sheet 1 and sheet 2)

2. Sheet 1 contained dates in column B starting at row 5
3. Sheet 1 contained product names in row 4, starting from column C
4. at the intersection of various dates and productions, you had codes like "d", "s" etc

5. Sheet 2 contained a date value in cell d4 against which you wanted to do a check up for products
6. Sheet 2 contained a multi-column table starting at d8. The header of the table had various customers and underneath each customer you had products

7. You wanted to changed the color of the product under each customer if the in sheet 1, for that date and product, there is a certain code.

Now this is how I approached it. I tried to stay away from macro. These are the steps

1. Name the range where the dates are as "DATES" ( in your sample, that would be sheet1, column B). Start at the first row (B1) (even though your data started at row 5)

2. Name the range where the products are as "PRODUCTS" ( in your sample, that would be sheet1, row 4). Start at the column A (even though your data started at column C)

3. go to to sheet 2 and Select all columns that had company names (in your sample that would start at D till G)

4 Go to conditional formatting and apply this formula
=INDIRECT(ADDRESS(MATCH($D$4,DATES,0),MATCH(D1,PRODUCTS,0),1,TRUE,"sheet1"))="d"

5. go to format and change the color to green

6 Go to conditional formatting and apply this formula
=INDIRECT(ADDRESS(MATCH($D$4,DATES,0),MATCH(D1,PRODUCTS,0),1,TRUE,"sheet1"))="s"

7. go to format and change the color to red
You can add similarly other format conditions


Here is the sample file with formatting.
https://authentification.site/files/21075943/CondtionalFormatXREF.xls
0
ShootingFish > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Feb 22, 2010 at 12:50 PM
I must say that you have impressed me, i never thought that conditional formatting would even accept code like that so i didn't even try it.

I always knew that there was a way to do this sort of thing. Thank you!
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > ShootingFish
Feb 22, 2010 at 01:57 PM
Well one would never know it till one give it a shot. I also had no idea whether this twisted way would work or not. Gave it a shot, and it worked!!! I am curious about performance in real life. I would appreciate if you could, down the road, give a feed back whether it had any adverse performance effect.
0
Answer 2 / 4
ShootingFish
Feb 28, 2010 at 08:27 AM
Some feedback:

Well I have tested it using the maximum possible number of calculations for one day (224), there was no delay in all of the colors changing so I would say that this method of doing what I wanted to do is perfect.

Using something like the same formula I have also created a sheet which counts the number of dispatch slots that are used each day, to analyse how full my trucks should be when they leave my site. It's all very useful.

However, I was toying with how to count the products by the type of packaging that was used and that failed.

My newest idea here is to use a drop down menu to select the type of packaging into a cell on the product matrix sheet and then getting a cell on another sheet to count the number of times that each type occurs on a date. So far I have the ability to count the number, that's the easy part. But I just can't get it to give me the number for the defined date.

Bottom line, it's all good and very useful. I'm just hoping that MS Office10 won't change from this too much, as I have learned so much and don't want it to become useless information when I upgrade!
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 28, 2010 at 08:36 AM
not exactly sure what you would like to do. On the surface it seem that COUNTIF should do the work for you.
0
Answer 3 / 4
ShootingFish
Feb 28, 2010 at 08:44 AM
"countif" is what I'm playing with here. In English, I am trying to tell the spreadsheet:

"Count all of the times when a dispatched product is packaged in × on the specified date"

In practice, this is hard to do! I have been able to get it to count how many times the packaging type is used - but that is information I already know, what I need to do is find a way to get it to look at the "d", and then identify the packaging type and return the number of occurrences for the specified date. Since there are 4 packaging types, i would be able to see not only the total number of products to be dispatched on a date, but also which packaging I am using and therefore, look at my costs and dispatch policy in more detail. The ability to forecast this would be useful in many ways but mostly, I am eager to learn more about formulas and see what they can do for me.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 28, 2010 at 08:48 AM
A picture is better than a thousand words. may be put a file up.

Also, a suggestion may be open a new thread on this, so that if down the road some one else is seeking same sort of issue, it just might help him.
0
Answer 4 / 4
ShootingFish
Feb 28, 2010 at 09:13 AM
https://ccm.net/forum/affich-275706-countif-based-on-two-criteria


I started a new thread for the new project
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
If cell A1 has text then cell B2 has today's Date
Sasha -
ac3mark -

1 response