Advanced Conditional Formatting? Is VBA the answer?

[munkifisht]

Ok, I am very unVBA savvy, well what I want to do is to change set the background colours and format of rows of cells based on the values of one of these cells. I have this working to some extent using conditional formatting where if the value of the cell is ‘Done’, ‘In Progress’ or ‘On Hold’ it changes the value of the row to a specific colour, the problem is I may have up to 10 different states for this.



Now, here is an example of one of my sheets:



____B____________|C________|D_____|E_______|F______|G___|H____|I______|J___|
8_|_Main_Task_No|_Task_No|Task__|Engineer|Status_|Done|To_Do|Planned|Over|
9_|_A1___________|_________|Task_M|________|To_Do__|____|_____|_______|____|
10|______________|A1.1_____|Task_1|_E.ng___|To_Do__|00__|04___|04_____|00__|
11|______________|_________|Totals|________|_______|Sum_|Sum__|Sum____|Sum_|




What I want the program to do is to see the state of cell f?? and then based on a key that I have created, I want the program to change the backgrounds of cells C?? to J?? based on this key, ie read the value, find the same value in the key, find the background colour of the cell next to that value, and apply that to all cells Bn-Jn where n is the value of the row. Also when the cell in B is not blank I want that to be filled as well and I also want the text to turn bold.



I was hoping also to have a list sorted on a separate page as well so you could select a value for column E and F from that list. From what I can see, excel only allows you to generate lists on the page you wish to display them, through validations that is. I also don’t want the main headings to be have this condition applied to them, ie the lists (rows 8, 9 and 10 are only chosen for convenience, in reality they may be any numbers) are only applied where they have to be, and before the totals are calculated. In all cases the sheets start the same way, ie with ‘main task no’ in cell B? and end the same with the totals, but other information is included before and after this.



Like I said I am pretty much a novice on VBA, so any help would be great.



Thanks.

 

[dcardno]

I'll start with a question (maybe more than one), and then end with a small start at a solution. This looks like the sort of table that would be assembled by adding (or updating) tasks / sub tasks as required (or loading from a project management database) then sorting by task / sub-task and using the Data | Subtotal command to add subtotals. In this case, you would "remove all" subtotals, re-sort the data, then add new subtotals as required. Is that your approach, or are all the totals, subtotal labels, etc, entered, with new tasks / subtasks inserted when required? I'm not sure how relevant that is - but no doubt the solution will make an implicit assumption that will be wrong, and will invalidate the suggested response...

Second - if you truly NEED 10 different formats, then you are probably stuck with VBA. On the other hand, if all you need is ten different tests that will exhibit three (or fewer) formats, then you can probably come to a 'conditional format' solution that woks. In making that decision, I would bear in mind that 10 formats could get pretty confusing, but you will have to make that determination, based on the size of the report and how much difference there really is between the different indicators.

Finally - it is possible to use a list on a seperate tab for the acceptable entries in a validation list. Make the list on another tab (I have only tried it as a vertical list) and name the range that contains the list of items - say "DefinedStatus" for the different categories of task status. In the status column (column F on your example) select an active cell (not the heading) and click Data | Validation and select "Allow... List." In the "Source" input box type "=DefinedStatus" (you need the = sign, you don't need the quotes). I would select "In-Cell Dropdown" and "Ignore Blank" - but that depends on your requirements. You can apply the validation criteria to the other cells in the column as required.

 

[munkifisht]

thanks for that dcardno. Re the first point, the sheet is only updated as new items are required, and this is done through excel, the spreadsheet acts as the database. To the second, 10 may be an exargation, but there will be more then 3, and that is where the problems really lie. I also work in an office where people don't really have that much programming exp (I've asked around) and it will need to be user freindly.To the third, if it is, that's a great help, I will apply that right now.

 

[dcardno]

Okay, set up a named range on another sheet to hold the allowed values for the "Status" column, and colour the cell to the right of each allowed name the way you want it to show up on the data sheet. The following VBA routine will colour examine each cell in the "Status" column, and if it matches a value from the allowed list (and it either has to match or be blank) it will colour the whole row appropriately. This routine assumes that the data sheet is active, so it should probably be run from a command button on the data sheet, and it assumes that the named range with the allowed list entries is called "WorkStatus" (the capitalization is not important, but I like it when I set up named ranges, as it makes the name easier to understand)

Sub SetColour()

Dim cll        As Range
Dim rgTestCol  As Range
Dim rgRef      As Range
Dim iMatch     As Integer

Set rgTestCol = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(5).EntireColumn)
Set rgRef = [workstatus].Cells(1)
ActiveSheet.UsedRange.Interior.ColorIndex = -4142
For Each cll In rgTestCol
    On Error Resume Next
    If cll.Value <> "" Then
        iMatch = WorksheetFunction.Match(cll.Value, [workstatus], 0)
        Intersect(cll.Rows.EntireRow, ActiveSheet.UsedRange).Interior.ColorIndex = _
        rgRef.Offset(iMatch - 1, 1).Interior.ColorIndex
    End If

Next cll

End Sub

The reference to "ActiveSheet.Columns(5)" specifies the "Status" column - if you change the layout of your worksheet you will have to adjust that accordingly. If you decide to change the colours, just make the change in the cells to the right of the WorkStatus list, then re-run the code. The routine will also examine the column headers, which will not return a match with the values in WorkStatus - this will return an error from the Match worksheetfunction (hence the need for "On Error Resume Next"), and set iMatch to 0. The Interior.ColorIndex property will be set based on the cell immediately above and to the right of the first cell of the WorkStatus range - so you will probably want to make sure that cell has no background colouring.