"On Change" event usage

[chiggy99]

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

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Long

If Not Intersect(Target, Range("J6:J70")) Is Nothing Then

With Target

Select Case True
Case .Value = "Not Started"
icolor = 10
Case .Value = "Not Applicable" And .Offset(0, 2).Value = 17
icolor = 15
Case .Value = "In Progress"
icolor = 4
Case .Value = "Bronze"
icolor = 46
Case .Value = "Gold"
icolor = 44
Case .Value = "Silver"
icolor = 15
Case .Value = "Waived"
icolor = 15
End Select

.Interior.ColorIndex = icolor
End With
End If
End Sub
[/vba]

[chiggy99]

Thanks alot for that, it worked perfectly.

Got one more question though.

The change and update works fine when i make a change anywhere on that worksheet, but i also want to run that auto update if i make a change on another sheet, how would i point to this method on change for another worksheet.

I hope that makes sense....

[Bob Phillips]

Add that or similar code to that worksheet's code module.