EXCEL 2003 - Changing row colour based on drop-down selection - more than 3 conditions

[paulstan]

Excel Workbook
	AA	AB	AC	AE
3	Status Will be a drop-down list containing 6 items	Review Date	Requested Info Drop-down	Cleared Drop-down
4	Item 1	06/06/2011	f	f
5	Item 2	07/06/2011	f	f
6	No Item - so No Fill	12/06/2011	f	f
7	Item 1	04/06/2011	f	f
8	Item 2	03/06/2011	Yes	f
9	Item 4	05/06/2011	f	Yes
...

I'll try and explain as clearly as I possibly can as it does get a little confusing!!

My problem comes in 2 stages:

Stage 1
The status Column (AA) will contain items that the user can select from a drop-down list (6 in total, for example Item 1, Item 2, Item 3, Item 4, Item 5, Item 6). When the user selects one of the Items, then the whole row will change colour depending on what Item was selected: it would change to Blue if Item 1 were selected; Green if Item 2 selected and so on.

Stage 2 (here goes...)
So, after getting the rows to change colours based on the drop-down selection, I need to throw something else into the mixer. I need to over-ride any of the selected row colours with either Red or Yellow. When the user has entered their data, they will put a review date in the appropriate column (AB) (please note I'm using the UK format for dates (dd/mm/yyyy)). This date will either be for a month or a week in the future. When this date is reached, I would like the appropriate row(s) to change to Red, so highlighting to the user that further action is required (See Red row in diagram). So, the user has seen a Red row and completed the appropriate action. If it is that the user still needs more information, then they will select Yes in the Requested Info Column (AC) - this will turn the row Yellow. If they don't need to Request more information then they go straight to column AE.

Finally, the Cleared column (AE) - if Yes is selected then change the row colour to No Fill.

I am aware that I may need to have a 'Select Case' in VB to list the items along with the colour index numbers alongside, for the 6 listed Items. Would the Red/Yellow 'thing' also need to be put into the VB code or could this be done via Conditional Formatting?

Many thanks for taking your most valuable time to read this.

Paul S

 

[paulstan]

With some help have managed to solve all problems I had, except one.

I managed to sort out the drop-down list with a Case Select. Having the row change colour to Red and/or Yellow also works fine when the criteria are met. The problem I'm experiencing comes with the Column AE. When I enter a YES in this column, I would like there to be no fill colour in the row. What is happening is that if there is an item showing in Column AA then the Select Case is taking priority over any conditional formatting I come up with regarding Col AE and selecting YES. Please see original post for diagram.

VB Code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
 
     If Application.Intersect(Target, Columns("AA")) Is Nothing Then Exit Sub
Select Case Target
Case "Item 1"
    Target.Offset(, -26).Resize(, 31).Interior.ColorIndex = 5
 
Case "Item 2"
    Target.Offset(, -26).Resize(, 31).Interior.ColorIndex = 6
 
Case "Item 3"
    Target.Offset(, -26).Resize(, 31).Interior.ColorIndex = 7
 
Case "Item 4"
    Target.Offset(, -26).Resize(, 31).Interior.ColorIndex = 8
 
Case "Item 5"
    Target.Offset(, -26).Resize(, 31).Interior.ColorIndex = 9
 
Case "Item 6"
    Target.Offset(, -26).Resize(, 31).Interior.ColorIndex = 10
 
Case Else
 
        'May or may not need this, depending on validation rules.
    End Select
End Sub

And below are the conditional formatting formulas:

=IF(AND($AE4="",$AB4<>"",$AC4="",$AB4<=TODAY()),TRUE,FALSE)

Will turn row RED

=IF(AND($AC4<>"",$AE4=""),TRUE,FALSE)

Will turn row YELLOW


In a nutshell, if YES is selected in Col AE then I would like all formatting removed from the selected row, showing as No Fill (as Row 9 in the example in my original post).

Will I need to add something into the VB code to deal with this,or will it be another conditional formatting?

Regards

Paul S

 

[paulstan]

*** SOLVED ***

Managed to solve the problem by adding a third Conditional Statement.

Thanks for taking the time to look.

Regards

Paul S