I couldn't resist the temptation to stick to CF exclusively
I did the main sheet and sheet CD311.
I couldn't resist the temptation to stick to CF exclusively
I did the main sheet and sheet CD311.
Thanks very much snb. I'll take a look.
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!!
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
Near the Priorities Enum paste this one and modify your existing Color Assignment routine to use these constantsSub ColorByIndex() Dim Cel As Range For Each Cel in Range("A1:A58") Cel.Interior.ColorIndex = Cel.Value Next End Sub
In the SetColors Routine, you will have to edit "Interior.Color" to"Interior.ColorIndex."Public Enum PriorityColors clrPriority0 = xlColorIndexAutomatic clrPriority1 = 37 'A blue clrPriority2 = 4 ' A Green clrPriority3 = 40 'an orangey color clrPriority4 = 3 ' Red End Enum
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 expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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 FunctionOption 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
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 EnumEnum 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 FunctionPublic 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 expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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.
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 expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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
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
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
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.
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.Basically if I delete a Task and the associated periodicity, hours or days, the rest of the row blanks out.
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 expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
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
I told you not to look.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
Well at least I'm learning........albeit veeeeery slooowly
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 expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ