Hi Everyone,

i am new to this forum, so if i dont give enough information here, then please let me know.

I have created a spreadsheet which requires 7 conditional formats to be used.

A range of cells J6 to J70 each have a list of 7 options, and based on which one you select, it change the colour of the cell...pretty straight forward.

I have overcome the multiple conditonal formats, by inserting some VBA code in the worksheet under the method:

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)[/VBA]

I have used cases to allow for the different conditions.

The problem i am having is that four of the conditions also need to take into account the value in the cell in the corresponding row, of column L.

I am having difficulty figuring out how to include this in the code.

This is what i have so far:

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("J6:J70")) Is Nothing Then
Select Case Target
Case "Not Started""
icolor = 10
Case "Not Applicable"
icolor = 15
Case "In Progress"
icolor = 4
Case "Bronze"
icolor = 46
Case "Gold"
icolor = 44
Case "Silver"
icolor = 15
Case "Waived"
icolor = 15
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
[/VBA]

EG.

If i select cell J6 to be "Not Applicable" i also need to check the value of L6 before i turn the cell grey