Change Cell Colour

[Nick]

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

 

[Patrick R]

Conditional formatting is the correct way.

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

 

[Norman Jones]

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.