Change Cell fill color

J

jskobel

New member
Joined
Mar 24, 2020
Messages
8
Reaction score
0
Points
0
Excel Version(s)
Office Professional Plus 2013
Hello,
I have searched the web for answers on how to do this, but to no avail. i would like to have a vba (or formula) to change the fill color of a cell based on when the task was completed and when the task is to be done again. For example, if i cleaned the stove on 1/15/20 i would like that task box to be filled in with a color and then based on the frequency of the task, fill in the cells of the coresponding dates when the task needs to be done. I have attached a sample of tasks that need to be done. The first task is completed with the cells filled in the way i would like to be able to do the rest of the sheet.

NOTE: on the Frequency column (B) the codes are as follows:
D = daily
Q = quarterly
Y = yearly
W = weekly
M = monthly
SA = semi-annual
2-5Y = 2 to 5 years
5Y = 5 years

Any and all help is greatly appreciated.
 
jskobel said:
The first task is completed with the cells filled in the way i would like to be able to do the rest of the sheet.
Click to expand...
I can't see this; can you specify where that is?
 
Sorry, I didn't realize that I picked the wrong file. Here is the new file with the cells filled in the way i want.
The location (Boiler) has 10 tasks that are to be done.


p45cal said:
I can't see this; can you specify where that is?
Click to expand...
 

Attachments

  • IV_PM.1.xlsm
    33.4 KB · Views: 13
This is a bit raw and could do with significant refinement.
It looks at each row and looks from right to left until it finds a cell with a fill of some kind (not No Fill) and stops there (this means any existing fill colours aren't touched, intervals are taken from the rightmost filled cell), then it moves to the right filling cells in at appropriate intervals with the same colour.
Since you've got weekly intervals, I used 4 and one third weeks for a month which will result in there being a mixture of 4 and 5 week intervals for that code, but it should end up with the right number of events per year.
I don't know what to do with 2-5Y = 2 to 5 years
Code: 
Sub blah()
WeeksFromCode = [{"D",1; "W",1; "Q",13; "SA",26; "Y",52; "5Y",260; "M",4.333}] 'lookup table to convert code to a number of weeks.
For Each rw In Sheets("Sheet1").Range("A3:BC115").Rows
  Freq = rw.Cells(2).Value
  StepSize = Application.VLookup(UCase(Application.Trim(rw.Cells(2).Value)), WeeksFromCode, 2, False) 'number of weeks
  If Not IsError(StepSize) Then 'if it is a valid frequency code in column B:
    For colm = rw.Cells.Count To 3 Step -1
      With rw.Cells(colm)
        If .Interior.ColorIndex <> xlNone Then
          ThisColour = .Interior.Color 'grab the colour used by the user.
          For c = colm To rw.Cells.Count Step StepSize
            rw.Cells(c).Interior.Color = ThisColour
          Next c
          Exit For 'abandon search from right to left
        End If
      End With
    Next colm
  End If
Next rw
End Sub
 
Last edited:
Thanks, that is what i am looking for. now i just need to figure out how to have it do the rest of the sheets
 
Last edited by a moderator:
After working with the macro all day, the macro is close to what i am looking for. i would like to be able to pick the color with the cell.interior.color function. and i would like to be able to have it check the values in row B to decide which color to use.
 
I'm guessing you meant column B; you could have conditional formatting set up in column B and the macro could take the colour off that, otherwise you'd need to say what colours you want, either a hard coded colour in the macro or a few cells somewhere with the colours you want using. You decide and come back with a file.
Also there has to be a way of marking the date of the first time the task is completed - describe how you intend to do that.
 
I hope this file is of some more use. I forgot to mention earlier when you stated that you didn't know what to do with 2-5Y = 2 to 5 Years. I looked at your formula and decided to put it at a 3 year interval (leaving the code in Column B as is. As far as determining the date the first time the task is completed, i was thinking that if a cell is filled with a color, it could look at the date at the top of the current column of the current location (i.e.: boiler, sawmill, etc) and then determine the date from that date. Also I was thinking about making another checklist sheet that has the locations listed and a completed on date for each task. When the task is completed, it can fill in the corresponding task and date and then auto fill the rest in reference to the date completed.
 

Attachments

  • IV_PM.2.xlsm
    60.1 KB · Views: 11
Last edited:
Code below uses the conditional formatting colour as the colour to use on a given row.
At the moment it's using the first cell it comes across looking right to left that isn't no-fill. If they're all no fill currently nothing happens.
You could manually fill a cell with (any) colour to tell the system when a job was last completed, make sure all cells to the right of that are no-fill and run the code - the code will only affect that cell (making sure the colour's correct) and the appropriate cells to the right of it.
Code: 
Sub blah()
WeeksFromCode = [{"D",1; "W",1; "Q",13; "SA",26; "Y",52; "2-5Y",156; "5Y",260; "M",4.333}]    'lookup table to convert code to a number of weeks.
For Each rw In Sheets("Sheet1").Range("A3:BC115").Rows
  Freq = rw.Cells(2).Value
  ThisColour = rw.Cells(2).DisplayFormat.Interior.Color    'grab the colour used by conditional formatting.
  StepSize = Application.VLookup(UCase(Application.Trim(rw.Cells(2).Value)), WeeksFromCode, 2, False)    'number of weeks
  If Not IsError(StepSize) Then    'if it is a valid frequency code in column B:
    For colm = rw.Cells.Count To 3 Step -1
      With rw.Cells(colm)
        If .Interior.ColorIndex <> xlNone Then
          For c = colm To rw.Cells.Count Step StepSize
            rw.Cells(c).Interior.Color = ThisColour
          Next c
          Exit For    'abandon search from right to left
        End If
      End With
    Next colm
  End If
Next rw
End Sub
 
Here's an approach using just conditional formatting, no VBA. Starting from your IV_PM.1 file I added two helper columns (which you can hide) to simplify the conditional formatting formula. It detects the first date entered in a row and highlights the subsequent due dates. Be careful, though; the conditional formatting can get messed up quickly if you modify the worksheet by indiscriminately cutting, copying and pasting!

Norm
 

Attachments

  • IV_PM.1NS.xlsm
    40.4 KB · Views: 8
p45cal for some reason when i copy and paste the code into my excel file, nothing happens when i run the code. i put the code into module one, where i have had the other code as well. i deleted the old code. upon running the code, nothing happens within the file, and there is no error message either.
 
Last edited:
jskobel said:
p45cal for some reason when i copy and paste the code into my excel file, nothing happens when i run the code. i put the code into module one, where i have had the other code as well. i deleted the old code. upon running the code, nothing happens within the file, and there is no error message either.
Click to expand...
That code is designed to work on Sheet1, and as I said: "If they're all no fill currently nothing happens." So you should fill some cells with colour before running the macro.
 
thanks NormS. i like the way it works, but don't know if this is the best approach. May need to be able to add or delete item within the list as things change.


NormS said:
Here's an approach using just conditional formatting, no VBA. Starting from your IV_PM.1 file I added two helper columns (which you can hide) to simplify the conditional formatting formula. It detects the first date entered in a row and highlights the subsequent due dates. Be careful, though; the conditional formatting can get messed up quickly if you modify the worksheet by indiscriminately cutting, copying and pasting!

Norm
Click to expand...
 
p45cal i didnt notice that the cells were a conditional format and not just a fill color. I did change column b to be a fill and then tried to use the macro again, but the same thing happened. sorry, im not sure what i got going on that is wrong. attached is the current file i am working with
 

Attachments

  • IV_PM.2.xlsm
    119.4 KB · Views: 7
jskobel said:
p45cal i didnt notice that the cells were a conditional format and not just a fill color. I did change column b to be a fill and then tried to use the macro again, but the same thing happened. sorry, im not sure what i got going on that is wrong. attached is the current file i am working with
Click to expand...
I haven't done anything with any conditional formatting.
Whether there is conditional formatting in column B or not, the macro will work, but there has to be at least one coloured cell in a given row for it to work on.
 
You must log in or register to reply here.
Back
Top