chiggy99
11-17-2008, 05:22 AM
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:
Private Sub Worksheet_Change(ByVal Target As Range)
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:
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
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
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:
Private Sub Worksheet_Change(ByVal Target As Range)
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:
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
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