Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Change Cell fill color

  1. #1
    Seeker jskobel's Avatar
    Join Date
    Mar 2020
    Posts
    8
    Articles
    0
    Excel Version
    Office Professional Plus 2013

    Change Cell fill color



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,713
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by jskobel View Post
    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.
    I can't see this; can you specify where that is?

  3. #3
    Seeker jskobel's Avatar
    Join Date
    Mar 2020
    Posts
    8
    Articles
    0
    Excel Version
    Office Professional Plus 2013
    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.


    Quote Originally Posted by p45cal View Post
    I can't see this; can you specify where that is?
    Attached Files Attached Files

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,713
    Articles
    0
    Excel Version
    365
    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 by p45cal; 2020-03-25 at 04:34 PM.

  5. #5
    Seeker jskobel's Avatar
    Join Date
    Mar 2020
    Posts
    8
    Articles
    0
    Excel Version
    Office Professional Plus 2013
    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 p45cal; 2020-03-25 at 05:18 PM.

  6. #6
    Seeker jskobel's Avatar
    Join Date
    Mar 2020
    Posts
    8
    Articles
    0
    Excel Version
    Office Professional Plus 2013
    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.

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,713
    Articles
    0
    Excel Version
    365
    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.

  8. #8
    Seeker jskobel's Avatar
    Join Date
    Mar 2020
    Posts
    8
    Articles
    0
    Excel Version
    Office Professional Plus 2013
    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.
    Attached Files Attached Files
    Last edited by jskobel; 2020-03-26 at 02:35 AM.

  9. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,713
    Articles
    0
    Excel Version
    365
    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

  10. #10
    Acolyte NormS's Avatar
    Join Date
    Jul 2017
    Posts
    94
    Articles
    0
    Excel Version
    Excel 2016 ProPlus
    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
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •