Formatting specific number of cells up and down based on value of a cell

[joseulloa22]

Hello
I am trying to have a formatting for this calendar. Each day contains 6 rows which contains information about the product to make. As soon as I enter SKU ID, all the others cells in that day are populated as I have formulas in those. What I am looking to achieve is that, once I enter the SKU ID and all the other 5 cells are populated, have all 6 cells change to a specific color as assigned in the calendar attached.
Your guidance is greatly appreciated.

Regards
Joe

 

[dreid1011]

How are the colors assigned? Other than looking specifically at this month.

 

[joseulloa22]

The colors would follow these designations

Thanks
Joe

 

[dreid1011]

The following set of rules will take care of rows A5:G10. You will need to copy these rules for each week in the month. IE: Copy each color rule for the next week A13:G18, A21:G26, etc.

Book1
	A	B	C	D	E	F	G
5		10800A	10800A	10793A	10799A	10791A	10791A
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:G10	Expression	=A$5="10791A"	text	NO
A5:G10	Expression	=A$5="10793A"	text	NO
A5:G10	Expression	=A$5="10799A"	text	NO
A5:G10	Expression	=A$5="10800A"	text	NO

Also to note, if the SKUs can change over time, it might be helpful to have a table like the one you showed in post #3, and the CF rules can be altered to reference the table instead of a hard coded value.

 

[joseulloa22]

Thank you so much. This works. However, I have a total of 65 products each with a unique color assigned. Any 7 of those particular products can be scheduled in any week. So that means that I would need to create for each week a set of 65 conditional formatting and given I schedule normally 12 weeks out, it would be very large number of conditional formatting. I wonder if there would be a more comprehensive approach to encompass conditional formatting those weeks at once at least rather rather one week at a time?

 

[dreid1011]

Thank you so much. This works. However, I have a total of 65 products each with a unique color assigned. Any 7 of those particular products can be scheduled in any week. So that means that I would need to create for each week a set of 65 conditional formatting and given I schedule normally 12 weeks out, it would be very large number of conditional formatting. I wonder if there would be a more comprehensive approach to encompass conditional formatting those weeks at once at least rather rather one week at a time?

Not through Conditional Formatting rules, but perhaps some VBA can get the job done.

 

[joseulloa22]

Hello,
Can anyone please help writing a VBA for this conditional formatting scenario?
Regards
Joe

 

[dreid1011]

Here is some VBA that will change the color for the 6 rows based on the SKU. It requires a separate sheet with the color/sku chart. And conveniently enough, when you delete a SKU, it changes the color to white. If you want no fill instead of white, we can adjust the code. The only downside is you have to install this code on every month sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim weeks As Range, ColorIndex As Worksheet
Dim fRow As Long

'These are the weeks in each month, up to 6 weeks.
Set weeks = Union(Range("A5:G10"), Range("A13:G18"), Range("A21:G26"), Range("A29:G34"), Range("A37:G42"), Range("A45:G50"))

'Change the sheet name "Sheet2" to match the sheet with the color chart.
Set ColorIndex = Sheets("Sheet2")

Application.EnableEvents = False
If Not Intersect(Target, weeks) Is Nothing And Target.CountLarge = 1 Then

    'Change the range "B2:B10" to match how many colors/skus you have in the color chart.
    fRow = ColorIndex.Range("B2:B10").Find(Target).Row
    
    Target.Resize(6, 1).Interior.Color = ColorIndex.Range("A" & fRow).Interior.Color
End If
Application.EnableEvents = True
End Sub

 

[joseulloa22]

Sorry for the late response. Thanks much for sharing the code. I will be applying it tonight.