Fill a cell with color based on a condition - Part 2

jbfrank

Active Member
Joined
Oct 13, 2003
Messages
290
My original post is below (as well as Jacob's answer), but I need further help on this.

In column A, I am entering 5-digit SKU numbers (e.g., 94510). In column B, I'm entering an order quantity. The SKU numbers in Column A have multiples that they can be ordered in. For example, if I enter SKU # 94510, I can only order that SKU in multiples of 30. However, if I order SKU #12345, I can only order that in multiples of 36. So, here's the more complicated question I should have asked in the first place.

I want to fill in the corresponding number of cells to the right with the color blue based on the order quantity. For example, if I order 30 cases of SKU #94510 (94510 is in A1, and 30 is in B1), then I want 1 cell C1 to fill with the color blue. If I order 60 cases of the same SKU, then I would want 2 cells (C1 & D1) to be filled with blue. Jacob's answer below does a great job of answering this question. But what if I change the SKU to a number that can only be ordered in multiples of 36? Obviously, I would want 1 cell filled in for every multiple of 36. There are of course different multiples for different SKU's. I have already set up a table that references what multiples are assigned to what SKU. Any help is greatly appreciated.



************************************
Is there a way to fill a cell with color based on a condition in another cell?

For example, if I enter the number 30 in cell A1, I would like cell A2 to fill with the color blue. However, if I enter the number 60 in cell A1, I would like cell A2 and cell A3 to fill with blue. The number in cell A1 will always be a multiple of 30, so obviously, for every multiple of 30 that I enter into cell A1, I would want the corresponding number of cells to be filled in to the right. (90 = 3 cells, 120 = 4 cells, etc.)

************************

Select A2 and then goto Format | Conditional Format

Select "Formula Is" and input

=IF($A$1>=30*(ROW(A1)),TRUE,FALSE)

Drag A2 down for as many cells as you want to be conditionally formatted.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Just add another coditional format (you can have 3) and copy the code from the first condition and change 30 to 36.

Also you should not make another post for the same topic. Just reply to you old topic and it will be moved to the top of the forum. That way people can see all the responces to your first post.
 

jbfrank

Active Member
Joined
Oct 13, 2003
Messages
290
The problem with that is that I have over 15 conditions. Some SKU's can be ordered in multiples of 30, others in multiples of 36, others in multiples of 45, and so on.
 

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675

ADVERTISEMENT

You could always do it in VB then you can have unlimited number of conditions. Trouble is you'd need to recalculate manually.

Here is a simple bit of code just to show you what I mean

Sub Macro1()
Set Rng = Range("B1:B2")
For Each c In Rng
multiple = c.Offset(0, 1).Value
Factor = Int(multiple / c.Value)

For x = 1 To Factor
c.Offset(0, 2 + x).Interior.ColorIndex = 43
Next
Next
End Sub
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
Maybe the problem is deeper than it appears, but I don't see why this is "impossible" or why the caluclation needs to be done manually.

A table has already been set up for SKUs and corresponding order increments. The only issue is how the SKU ends up in column A. If it is manually entered, then with a sheet change event, the Target SKU value is found in the table, its order increment number is identified, its quotient is determined with simple math, and the cell(s) to the right of column A are shaded. Am I missing something?

There are hundreds of Change examples on this board using the Search feature, so code some simple math to find the quotient of interest. You would need to make sure that this table of yours does not contain ambiguous SKU order increment criteria. For example, if one SKU can only be done in 5, and another only in 15, then you would not want to have a 30 or 45 or 60 SKU order increment mandate...stuff like that.
 

mtnbike_jr

New Member
Joined
Mar 12, 2004
Messages
18

ADVERTISEMENT

conditional formatting evaluates the cell's value with either:
1. A static value that the user specifies
2. A variable value based on the value of another cell in the spreadsheet. 3. A static formula.

Based on your desire to create a simple histogram for the number of order multiples included in a total order for a SKU# by coloring each cell, you will have to set up conditional formats for each cell included in the "color range" (range of cells that will turn blue based on some value)

Also, each of these cells in the "color range" will have to have a formula that can be evaluated by the conditional formatting. You can set the default font color to white so that the formula result cannot be seen. The conditional formatting would change the font to blue to match the cell color so it still won't be seen.

I suggest the following:
Cell A1 - SKU#
Cell B1 - Order Qty
Cell C1 - Formula: Vlookup formula for the qty multiple
Cell D1 - Formula: =Round(B1/C1,0)
- for the number of multiples in the order qty.
Cell E1 - Formula: =IF($D1-(COUNTIF($B1:D1,"Y"))>=1,"Y","")
- will put a "Y" value in the cell.

Copy the formula in cell E1 & paste into cells F1:Z1 (you can pick the range needed). Then change the font color to white.
Copy range C1:Z1 down for every record in your table.

All cells that need to be colored blue will have a value of "Y".
Set the conditional format for range E1:Z?? where the cell font color and pattern fill are blue.

I already built this real quick to test it out and it seems to work the way you want it to.

Good Luck.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
mtnbike_jr - -How does your CF solution overcome the 3-condition (4-condition including no condition) maximum, when the OP said there are 15 conditions?
 

mtnbike_jr

New Member
Joined
Mar 12, 2004
Messages
18
only one condition! If cell value is equal to "Y", turn cell blue, else no conditional format.

Must copy conditional format to all cells within "color range"
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
Still not making sense. Did you read this from the OP's second post on this thread? - -
jbfrank said:
The problem with that is that I have over 15 conditions. Some SKU's can be ordered in multiples of 30, others in multiples of 36, others in multiples of 45, and so on.
Each cell in column B, C, D, etc would need to adapt to whatever SKU is in the neighboring cell in column A.

Also, your idea of formatting font the same as the cell color would not totally negate the possibility of the value being seen, for instance when the cell is part of a selected area. Custom formatting as ;;; would achieve that.

Maybe you understand the problem better than I do...I just don't understand how, if up to 15 conditions need to be readily available, you can use Conditional Formatting, at least on its own without VBA. In a sheet change event you can re-program CF to have a formula react to the SKU but as long as you are doing that why not just use VBA to manipulate the color index number.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,686
Members
425,230
Latest member
DzOus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top