Hi,
I've entered in the following code:
The problem I'm facing is that when I do select a cell and change it to any of the following options, it colours momentarily and then as soon as I go to another cell, it reverts back to normal.
How do I ensure that it stays a certain colour as long as the cell has that particular value?
Thanks for your help in advance. You people who answer questions from random strangers are angels!!!
:-) - Tina

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("i6:Ae28")) Is Nothing Then
With Target
Select Case .Value
Case Is = "AOD"
.Font.Bold = True
.Interior.ColorIndex = 36
Case Is = "MISC"
.Font.Bold = True
.Interior.ColorIndex = 40
Case Is = "PH"
.Font.Bold = True
.Interior.ColorIndex = 15
Case Else
.Font.Bold = False
.Interior.ColorIndex = 0
End Select
End With
End If
End Sub

I couldn't access ur file due to a firewall at work.

In the sheet, I do have code to highlight the row and column of any selected cell, it's a separate code...would that have anything to do with it?

However,the normal conditional formating (the limited 3 provided by Excel) is working perfectly.

Ok,this is what I have...just to confirm what ur saying is right...

is the following code preventing the cell to remain the colour specified for that text if i move to another cell?

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)


Static rr
Static cc

If cc <> "" Then
With Columns(cc).Interior
.ColorIndex = xlNone
End With
With Rows(rr).Interior
.ColorIndex = xlNone
End With
End If

r = Selection.Row
c = Selection.Column
rr = r
cc = c

With Columns(c).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With
With Rows(r).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Set MyPage = Range("i6:af28")
For Each Cell In MyPage

If Cell.Value = "AOD" Then
Cell.Interior.ColorIndex = 36
End If
If Cell.Value = "MISC" Then
Cell.Interior.ColorIndex = 16
End If
If Cell.Value = "PH" Then
Cell.Interior.ColorIndex = 15
End If

If Cell.Value <> "AOD" And Cell.Value <> "Misc" And Cell.Value <> "PH" Then
Cell.Interior.ColorIndex = xlNone
End If

Next
End Sub

Yes I want the colour to stay just like in the normal way of conditional formatting.

If cell A1 = AOD, then cell should be coloured yellow.

Just because I go to cell A2, A1 shouldn't lose the colour yellow while the cell value is still AOD.

Is there a way to do this while still being able to hightlight row and column of a cell?

That worked perfectly! and thank you for your prompt response!!!
Thank you so much!!!! ;-)