Change Cell Colour
 

I want to be able to change the background colour of a
cell if it has specific text in it e.g. if it
has 'Closed' in it i want to become Green. Conditional
formatting does not have enough options.

Cheers

Nick

Change Cell Colour
 

Conditional formatting is the correct way.

Should read Cell Value equal to Closed,
then under formatting, go to the patterns tab and select
green.


-----Original Message-----
I want to be able to change the background colour of a
cell if it has specific text in it e.g. if it
has 'Closed' in it i want to become Green. Conditional
formatting does not have enough options.

Cheers

Nick
.

Change Cell Colour
 

Hi Nick,

Try pasting the following into the relevant worksheet module (Right-click
the sheet tab | View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
On Error GoTo CleanUp:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:B10")) Is Nothing Then
For Each rCell In Target
With rCell
Select Case LCase(.Value)
Case "closed": .Interior.ColorIndex = 4
Case "open": .Interior.ColorIndex = 3
Case "ajar": .Interior.ColorIndex = 5
Case "obstructed": .Interior.ColorIndex = 7
Case "hidden": .Interior.ColorIndex = 8
Case Else: .Interior.ColorIndex = xlNone
End Select
End With
Next
End If

CleanUp:
Application.EnableEvents = True
End Sub

Change Range("A1:B10") to to reflect the cells you need to format.
Similarly, amend/add Case values and ColorIndex values to suit.


---
Regards,
Norman



"Nick" wrote in message
...
I want to be able to change the background colour of a
cell if it has specific text in it e.g. if it
has 'Closed' in it i want to become Green. Conditional
formatting does not have enough options.

Cheers

Nick