I couldn't resist the temptation to stick to CF exclusively
I did the main sheet and sheet CD311.
Printable View
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 constantsCode:Sub 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."Code: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
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...
Code: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
Code: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
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.
Code: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
Code: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
Code: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
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'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.
Code:=IF(C11 <> "",subtime($E$5,E11),"")
Current PriorityColores List
Code: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:banghead:
Code: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!
Code: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.Quote:
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.
Ok SamT,
Please see if this doesn't cause you any grief. Hopefully its not Read Only now: pray2:
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. :D
Well at least I'm learning........albeit veeeeery slooowly:rotlaugh:
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.