Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 76 of 76

Thread: Colour Entire Row Base on the Value of Two Other Cells

  1. #61
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,744
    I couldn't resist the temptation to stick to CF exclusively
    I did the main sheet and sheet CD311.
    Attached Files Attached Files

  2. #62
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Thanks very much snb. I'll take a look.

  3. #63
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Hi SamT,

    I pm'd you.

    Final question for you. If I delete a Task from column "A"; at present, the adjacent cell "D" remains colour filled, but changes from whatever priority colour it was to Black filled. How can I now reset the cell colour fill in column "D" to no fill. Basically what's happening is that after sorting the Task column, Column "D" has black filled cells at the bottom where I have deleted Tasks.

    Everything else is sweet!!

  4. #64
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    You can try setting it to vbApplicationWorkspace which might be white, or xlNone, which I have seen but never used. What you really want is to set the Interior's ColorIndex Property to xlColorIndexAutomatic.

    The Interior's Color Property is both the simplest and the most complex way to set it's color. Simplest because you can use VBA's 8 ColorConstants, as we have been doing, and complex because you can use the RGB function to set it to any custom color you desire. IIRC, you've done that for one color.

    The use of the ColorIndex Property lets you choose from any of the 56 colors in the color picker chart available when you format a Cells Fill. This has the advantage that you an modify any color in any Workbook by using the Color Tab in Excel's Tools >> Options Menu.

    To set this up, you need to know the index numbers of the Color Picker Chart, which are not intuitive. In a column put these numbers in individual cells, -4105 (xlColorIndexAutomatic,) -4142 (xlNone and xlColorIndexNone), and the next 56 cells with the numbers 1 to 56.

    Then run this routine
    Sub ColorByIndex()
    Dim Cel As Range
    
    For Each Cel in Range("A1:A58")
    Cel.Interior.ColorIndex = Cel.Value
    Next
    End Sub
    Near the Priorities Enum paste this one and modify your existing Color Assignment routine to use these constants
    Public Enum PriorityColors
      clrPriority0 = xlColorIndexAutomatic
      clrPriority1 = 37    'A blue
      clrPriority2 = 4     ' A Green
      clrPriority3 = 40   'an orangey color
      clrPriority4 = 3     ' Red
     End Enum
    In the SetColors Routine, you will have to edit "Interior.Color" to"Interior.ColorIndex."

    Run the routine to set your sheet's colors and then go into Excel's Options Dialog and on the Color Tab, select a color and click Modify. Go the the Custom Tab and play around. Use the generated list of colors by index to assign values to the Enum PriorityColors' Cosntants
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #65
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location

    Getting some weird results

    Getting weird results. I've tried various combinations as it appears as though there will be two sets of Enums referring to priorities and colours in a duplicate kind of way!!! Is it because I need to remove the old Enum or add the second Enum. If I remove the old enum Priorities, I get undefined doofries etc. ? Basically if the TaskCol is empty from deleting a Task, there should be no colour in the "D" column.

    My modPriorities and modCellColoring as below...

    Option Explicit
    
    Enum Priorities
      Priority0
      Priority1
      Priority2
      Priority3
      Priority4
    End Enum
    
    
    
    
    Function PriorityDays(Cel As Range) As Long
        If Cel.Value = "" Then
        PriorityDays = Priority0
        Exit Function
      End If
      
    Select Case Cel.Value
      Case Is <= 0: PriorityDays = Priority4   'RED
      Case Is <= 7: PriorityDays = Priority3    'ORANGE
      Case Is <= 30: PriorityDays = Priority2   'GREEN
      Case Is <= 60: PriorityDays = Priority1    'BLUE
      Case Else: PriorityDays = Priority0        'BLACK
    End Select
    End Function
    
    
    Function PriorityHours(Cel As Range) As Long
      If Cel.Value = "" Then
        PriorityHours = Priority0
        Exit Function
      End If
      
    Select Case Cel.Value
      Case Is <= 0:  PriorityHours = Priority4   'RED
      Case Is <= 20: PriorityHours = Priority3   'ORANGE
      Case Is <= 50: PriorityHours = Priority2   'GREEN
      Case Is <= 100: PriorityHours = Priority1   'BLUE
      Case Else: PriorityHours = Priority0        'BLACK
    End Select
    End Function
    
    
    Function ColorByPriority(priority As Long) As Long
      Select Case priority
      'BLACK
        Case 0: ColorByPriority = vbBlack
        'BLUE
        Case 1: ColorByPriority = vbBlue
        'GREEN
        Case 2: ColorByPriority = RGB(51, 204, 51)
        'ORANGE
        Case 3: ColorByPriority = RGB(255, 153, 0)
        'RED
        Case 4: ColorByPriority = vbRed
      End Select
    End Function
    Option Explicit 
    Public Sub SetColors(sht As Worksheet, Cel As Range)
         
        Dim TimePriority As Long
        Dim TimeColor As Long
        Dim DatePriority As Long
        Dim DateColor As Long
        Dim TaskColor As Long
        Dim TaskPriority As Long
        Dim Rw As Long
        Dim i As Long
        
                 
        With sht
            Rw = Cel.Row
            TimePriority = PriorityHours(Cells(Rw, HoursRemainingCol))
            DatePriority = PriorityDays(Cells(Rw, DaysRemainingCol)) '<<<Changed from your Sheet Code!!!!
             
            TimeColor = ColorByPriority(TimePriority)
            DateColor = ColorByPriority(DatePriority)
            TaskColor = TimeColor
            If TimePriority < DatePriority Then TaskColor = DateColor
            
                                  
            With .Cells(Rw, TaskCol).Font
                .Color = TaskColor
                .Bold = False
                
                If TaskPriority >= Priority4 Then .Bold = True
                
            End With
            
            .Cells(Rw, BLankCol1).Interior.Color = TaskColor 'Adjust to suit
             
            ColoredHoursCells = Array("G", "I")
            For i = LBound(ColoredHoursCells) To UBound(ColoredHoursCells)
                .Rows(Rw).Columns(ColoredHoursCells(i)).Font.Color = TimeColor
                
            Next i
             
            ColoredDaysCells = Array("N", "O")
            For i = LBound(ColoredDaysCells) To UBound(ColoredDaysCells)
                .Rows(Rw).Columns(ColoredDaysCells(i)).Font.Color = DateColor
            Next i
                     
        End With 
            
    End Sub

  6. #66
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    An Enum Statement is handy for when you need a bunch of similarly named Constants and their actual values aren't important. Note that in your previous code, The actual values of the Priorities Constants were only used in the ColorByPriority Function to select an actual color. Ironically, the color was just another blind Constant whose actual value was not important to you, the programmer.

    BTW, Enum stands for Enumerated Constants. If you don't assign values to the Constants, their values will start at 0 and increment by 1 for each consecutive Constant.

    Note that the actual Values of the Enums are passed bwtween the various Functions and Sub SetColors.

    I would suggest that you place the Enum PriorityColrs in modVariablesAndConstants, since that is where all other Constant Values that may need to be changed are located.
    Public Enum PriorityColors
     'Resulting Color in Comments
     'Set = to selected Color Picker Index number
        clrPriority0 = xlColorIndexAutomatic 'Color of Excel's Standard Font (= -4105)
        clrPriority1 = 37 'A blue
        clrPriority2 = 4  'A Green
        clrPriority3 = 40 'an orangey color
        clrPriority4 = 3  'Red
    End Enum
    Enum Priorities
    'Actual values in Comments
        Priority0 '0
        Priority1 '1
        Priority2 '2
        Priority3 '3
        Priority4 '4
    End Enum
     
     
    Function PriorityDays(Cel As Range) As Long
        If Cel.Value = "" Then
            PriorityDays = Priority0
            Exit Function
        End If
         
        Select Case Cel.Value
        Case Is <= 0: PriorityDays = Priority4
        Case Is <= 7: PriorityDays = Priority3
        Case Is <= 30: PriorityDays = Priority2
        Case Is <= 60: PriorityDays = Priority1
        Case Else: PriorityDays = Priority0
        End Select
    End Function
     
     
    Function PriorityHours(Cel As Range) As Long
        If Cel.Value = "" Then
            PriorityHours = Priority0
            Exit Function
        End If
         
        Select Case Cel.Value
        Case Is <= 0:  PriorityHours = Priority4
        Case Is <= 20: PriorityHours = Priority3
        Case Is <= 50: PriorityHours = Priority2
        Case Is <= 100: PriorityHours = Priority1
        Case Else: PriorityHours = Priority0
        End Select
    End Function
     
     
    Function ColorByPriority(priority As Long) As Long
        Select Case priority
        Case Priority0: ColorByPriority = clrPriority0
        Case Priority1: ColorByPriority = clrPriority1
        Case Priority2: ColorByPriority = clrPriority2
        Case Priority3: ColorByPriority = clrPriority3
        Case Priority4: ColorByPriority = clrPriority4
        End Select
    End Function
    Public Sub SetColors(sht As Worksheet, Cel As Range)
         
        Dim TimePriority As Long
        Dim TimeColor As Long
        Dim DatePriority As Long
        Dim DateColor As Long
        Dim TaskColor As Long
        Dim TaskPriority As Long
        Dim Rw As Long
        Dim i As Long
    
        With sht
            Rw = Cel.Row
            TimePriority = PriorityHours(Cells(Rw, HoursRemainingCol))
            DatePriority = PriorityDays(Cells(Rw, DaysRemainingCol)) '<<<Changed from your Sheet Code!!!!
             
            TimeColor = ColorByPriority(TimePriority)
            DateColor = ColorByPriority(DatePriority)
            TaskColor = TimeColor
            If TimePriority < DatePriority Then TaskColor = DateColor
             
            With .Cells(Rw, TaskCol).Font
                .Color = TaskColor
                .Bold = False
                If TaskPriority >= Priority4 Then .Bold = True
            End With
             
            .Cells(Rw, BLankCol1).Interior.ColorIndex = TaskColor 'Adjust to suit
             
            ColoredHoursCells = Array("G", "I")
            For i = LBound(ColoredHoursCells) To UBound(ColoredHoursCells)
                .Rows(Rw).Columns(ColoredHoursCells(i)).Font.Color = TimeColor
            Next i
             
            ColoredDaysCells = Array("N", "O")
            For i = LBound(ColoredDaysCells) To UBound(ColoredDaysCells)
                .Rows(Rw).Columns(ColoredDaysCells(i)).Font.Color = DateColor
            Next i
        End With
    End Sub
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  7. #67
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    OK, colours are working in column "D". Some of the cells in that column, that are not colored, don't appear to have borders. Also, the font in columns "A", "G", "I", "N" and "O" are really faint to the point of being unreadable. Not sure whats happening there.

  8. #68
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    Go back to my post where I showed you how to discover the index numbers in the Color Picker chart, and adjust the values of the PriorityColors Constants as desired. I just picked some that looked close.Unfortunately, you can't use vbBlack if you're going to color interiors. AFAIK, there is no vbNone color constant.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  9. #69
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location

    Added Priority Level and Code Upload

    I've had to add a priority level in order to have Black as an option for cell and font colour for values greater than 100 hours or 60 days.
    This then leaves clrPriority0 for -4142 (xlNone and xlColorIndexNone. I did however have to reverse the order of priorities for the bold option(I think).

    The font is still not changing colour as desired (my lack of coding skill), so I still have not grasped the entire picture. My row formulas currently use hour and calendar period row "empty check" formulas. This means there are no negative or strange numbers if Hours or Calendar Period are empty .

    Basically if I delete a Task and the associated periodicity, hours or days, the rest of the row blanks out. If you look at CD11 first row, there is an exhaust inspection in faint text which occurred when I deleted the rest of the row. Really when I delete the Task, the coloured cell in column "D" should lose any colour. File attached for your perusal.

    Sample blank check formula. Subtime is a sub for time calculations.
    =IF(C11 <> "",subtime($E$5,E11),"")

    Current PriorityColores List
    Public Enum PriorityColors     'Resulting Color in Comments
         'Set = to selected Color Picker Index number
        clrPriority0 = -4142
        clrPriority1 = 1 'Black
        clrPriority2 = 37 'A blue
        clrPriority3 = 4 'A Green
        clrPriority4 = 40 'an orangey color
        clrPriority5 = 3 'Red
        
    End Enum

  10. #70
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Just found part of my mistake. Font.ColorIndex. Duhhh
    Public Sub SetColors(sht As Worksheet, Cel As Range)     
        Dim TimePriority As Long
        Dim TimeColor As Long
        Dim DatePriority As Long
        Dim DateColor As Long
        Dim TaskColor As Long
        Dim TaskPriority As Long
        Dim Rw As Long
        Dim i As Long
         
        With sht
            Rw = Cel.Row
            TimePriority = PriorityHours(Cells(Rw, HoursRemainingCol))
            DatePriority = PriorityDays(Cells(Rw, DaysRemainingCol)) '<<<Changed from your Sheet Code!!!!
             
            TimeColor = ColorByPriority(TimePriority)
            DateColor = ColorByPriority(DatePriority)
            TaskColor = TimeColor
            If TimePriority < DatePriority Then TaskColor = DateColor
             
            With .Cells(Rw, TaskCol).Font
                .Color = TaskColor
                .Bold = False
                If TaskPriority >= Priority5 Then .Bold = True
            End With
             
            .Cells(Rw, BLankCol1).Interior.ColorIndex = TaskColor 'Adjust to suit
             
             
            ColoredHoursCells = Array("G", "I")
            For i = LBound(ColoredHoursCells) To UBound(ColoredHoursCells)
                .Rows(Rw).Columns(ColoredHoursCells(i)).Font.ColorIndex = TimeColor
            Next i
            
            
             
            ColoredDaysCells = Array("N", "O")
            For i = LBound(ColoredDaysCells) To UBound(ColoredDaysCells)
                .Rows(Rw).Columns(ColoredDaysCells(i)).Font.ColorIndex = DateColor
            Next i
        End With
    End Sub

  11. #71
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    And I've just found for the Task column font colour.
    Bold is not working for <=0 , so thats my next issue!

    With .Cells(Rw, TaskCol).Font.ColorIndex = TaskColor
                .Bold = False
    
                If TaskPriority >= Priority5 Then .Bold = True
    
            End With

  12. #72
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    If your font is normally black, why do you need to have a color "Black?" -4105, the automatic color, will set the font to black. I am not sure that the Font object will accept -4142 as an option. It might, but it doesn't look like it from your sheets.

    Basically if I delete a Task and the associated periodicity, hours or days, the rest of the row blanks out.
    You didn't delete the task, but I know what you mean. I am assuming that that row should just go to unformatted with color.

    You added Case Else: PriorityDays = Priority0 to the Priority (Days and Horus) subs, but there is no Else so that is never reached. Priorty0 is only ever assigned when the Cel.Value is "". Also, as long as a ColorIndex of 1 (Black) can be assigned, you will have black cells.

    When you tested my submitted code, as I wrote it, what were the results?

    I was going to simplify the code in Main, but you've changed things and I don't know what you are doing with it.

    Your attachment is so protected that I can't work with it to see what is going on. It is also READ ONLY, and I am not going thru that hassle again. This is a development book and should not be protected, read only, or have any active "On Err GoTo"s or "DisplayAlerts = False" in it. Any attribute that prevents the programmer from seeing everything that is happening and/or keeps him from doing anything she wants to a WIP should not be set.

    Go back to the book you were using before my last code submission. Add and edit the code as in my submission. Unprotect all cells and all sheets. Save the book, but NOT as Read Only. Close and reopen the book. Test the book. Note any failures, and, if any errors the sub and line they occur on. Upload the book here without any other edits to the code.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  13. #73
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location

    Coded as Advised

    Ok SamT,

    Please see if this doesn't cause you any grief. Hopefully its not Read Only now

    I believe I've followed your instructions as advised with the changes you posted both in code and instructions.

    Cheers

  14. #74
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    I told you not to look.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  15. #75
    VBAX Regular
    Joined
    Jun 2015
    Posts
    67
    Location
    Well at least I'm learning........albeit veeeeery slooowly

  16. #76
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,077
    Location
    SamT will be gone for a while

    If you need more help, start another thread and reference this on in it. That will get more people working on it. Sorry.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Posting Permissions

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