Conditional Script

[Steph]

Hi all, due to the excellence of this forum, I was able to use some VBA code
for additional CF's. I am using this:

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "B7:H45"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case "HEROLD": .Interior.ColorIndex = 4 'red
Case "ROUSSEAU": .Interior.ColorIndex = 4 'yellow
Case "ELLIOTT": .Interior.ColorIndex = 4 'blue
Case "BACKHOLM": .Interior.ColorIndex = 4 'green
Case "HARLAND": .Interior.ColorIndex = 4 'red
Case "CHALKER": .Interior.ColorIndex = 4 'yellow
Case "MACLEAN": .Interior.ColorIndex = 4 'blue
Case "JORDAN": .Interior.ColorIndex = 4 'green
Case "LEAZOTT": .Interior.ColorIndex = 4 'red
Case "KENT": .Interior.ColorIndex = 4 'yellow
Case "CARON": .Interior.ColorIndex = 4 'blue
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


My question is that once a name is removed, the cell remains the new color.
is there a way to set it back to default, as in Access?

Second question: I have a Workbook with 4 tabs. The first one is a
COMBINED tab, which has linked cells from the other 3, compiled together.
The formatting in my VBA code is not carrying over to the combined. is this
possible, I know in Access I can do a me.refresh. Does Excel have anything
like that?

Thanks so much in advance!

Steph

 

[Per Jessen]

Use 'Case Else' to reset color:
'---CUT----
Case "CARON": .Interior.ColorIndex = 4 'blue
Case Else
.Interior.ColorIndex = 0
End Select
'---CUT---

You can not carry over formatting, so you have to create a similar macro for
your compiled sheet.

Hopes this helps.
....
Per

 

[Steph]

Ok, getting greedy time! If i have for example:

Case "HEROLD": .Interior.ColorIndex = 4

which changes cell background, how can i change the font color as well?

Thanks a million