Macro code to copy cell color to another cell if condition is met

[hamkham93]

Hi. I'm new to VBA and I'm need help to create a code for tracking a recurring task on a schedule at different frequencies.

1- I used Excel formula to generate a color value for the colors I am using.
2- Using row 5 as an example.

• If row 5 (Area1) contains the color value "42" (blue) it should copy the cell same color, move it 31 cells to the right and paste blue color (which should be cell AG5 - I copied it manually for the example)
• I would like to the code to do this for any of the 4 colors listed for rows 5 to 9

Thank you in advance!

 

[Micron]

So in rows 6,7,8,9 nothing would be copied?

 

[hamkham93]

So in rows 6,7,8,9 nothing would be copied?

All rows follows the idea on row 5. I had an example on Area2 and Area4 (box bordered in red)

 

[Micron]

All rows follows the idea on row 5.

I get that. I'm asking if based on the example you posted, no colour copying would occur in those rows because col B cells interior colours do not match any of the 4 samples. That is based on
If row 5 (Area1) contains the color value ... for any of the 4 colors listed and that list appears to be in U12 to U15.
Neither of the red bordered cells are 31 cells over from the other colour to the left, which aren't in column B at all. Hence the question.

 

[hamkham93]

Sorry for not being clearer in my first question. The light green/grey color in row 6 and 8 are just table format coloring, they are not part of any color coding). So I assume part of the code will have if cell color isn't any of the 4 colors (U12-U15), then skip. I can make the table row 5 to 9 no fill if that's easier.

- Each color has different days/spacing (brown = 90, blue= 31, yellow= 7, orange= 14)
- The color correspond to the day a task was performed. So they can start at any day (column B and onward)
- Like in row 6, the yellow box in O6 is 7 cell away from cell H6 (yellow is weekly rate)

 

[Micron]

Each color has different days/spacing (brown = 90, blue= 31, yellow= 7, orange= 14)

That's vital information:

The color correspond to the day a task was performed. So they can start at any day (column B and onward)

So put another way, in rows 5 to 9 you want to find the last cell going from left to right whose interior colour is one of those 4 values.
Then offset a number of cells to the right based on a number associated with that colour and copy that colour to that location.

If that's not it then I have no idea where the starting points are in any of those rows.
Must go out now. Will check back later.

 

[hamkham93]

If that's not it then I have no idea where the starting points are in any of those rows.

Your explanation is correct, I want to look from the left (starting from column B) to the right. The reason for that is each area (row 5-9) will have tasks first starting date at differed day of the months.
Can we have a code that looks for first cell in row 5-9 whose interior color is one of those 4 values or you need to specify the first colored column cell to start with?
Thank you!

 

[Micron]

a code that looks for first cell in row 5-9 whose interior color is one of those 4 values

Don't you mean the last cell, not the first?
If today it is H6 as you show and said code is run, O6 becomes coloured as you show.
Next time it runs, the first one is still H6 so . . . O6 is still the one that gets coloured because of that. If you don't "un-colour" H6 it remains the first. If you look for the last one instead it is O6 so V6 (?) is the next one to be coloured. Or is H6 supposed to be un-done?

You really need to think through what it is you are describing and leave nothing to interpretation. If I help and don't do it right and it's my fault, I'd fix it if I can. If you move the goal posts or give out the puzzle pieces one at a time, there's no incentive to start or to fix anything because one never knows where it will end. I don't think this would be all that difficult - but it's critical to understand exactly what somebody wants or it's a waste of time.

 

[Micron]

Took a lot (for me) but I have something that seems to work. Something I don't understand is, why you show 5 rows and only 4 colours.
Right now, I find the range from B5 to row 9 as far as the last used column - whichever row that is. I have 4 colours from row 5 to 9. Since row 9 has no colour in it, nothing happens. I have not tested this by having a row in between with no colour in it, but I don't see why that would raise an issue. Unfortunately it does not seem possible to get the last used column (not the same as the last column with data) for a particular row. So, the fact that one row increases by 90 and another by only 7 the used range will end up being very wide when compared to rows that are only coloured for much smaller increments. Seems there is nothing I can do about that since you have no data in those cells.

If you want the code as is and you will tweak it, let me know. Otherwise, I have to know the proper colour numbers you're using (and the 5th one if it exists). To do that, open the vba editor (alt+F11). The immediate window should be showing at the very bottom. Make sure your sheet is the active sheet. Note the cell address for each colour (e.g. B5) and in immediate window, type
?Range("B5").Interior.Color and press return Yes, do include the question mark. The cell colour should appear below: e.g. 14395790
Take note of which colour value you get for each row you intend to have coloured cells in.

This is after running it 2x but I have to pick smaller numbers than 90

 

[hamkham93]

Can we have a code that looks for first cell in row 5-9 whose interior color is one of those 4 values or you need to specify the first colored column cell to start with?

I am sorry, what I meant by first is to look for the first cell with the color and then start skipping but the next colored cell to generate a new colored cell won't be the last. So "If you look for the last one instead it is O6 so V6 (?) is the next one to be colored" is correct. and H6 should remain there, not un-done. But I am reading your second message, and it seems you can't, that's fine

The five rows represent 5 areas in a factory. Each area have different machines that are serviced at different frequency (4 colors). The colors can be the same in each area (row). See the attached excel for example. The goal is to create a something that would track when we do a service and when is the next time. Often times we don't follow the frequency because the machine goes down due to unexpected issue, so that would delay the frequency service. So I want something that would look for the color in each of the 5 rows, match it with the color value (M14-17) and decides to skip based on that, only one time. that way I will know when is the next time we need to service it. If something happened, and we service a machine after the scheduled time, I will add a colored cell on the new date the service will be made so it can show me the next service due date.

I have not thought about this into all details, sorry if I am giving you new information in pieces, hopefully its more clear now.