Vba conditional formatting [Closed]

Report
-
 Tina -
Hello,

5 replies

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 am using Office 2007, but I saved the workbook as 97-2003. I used your code and just randomly entered values in the range based on your criteria, but I did not get the issue you were referring to. Maybe you can try the file I used and see.

[URL=http://www.4shared.com/file/90418748/420881a4/Color_Cells.html]Color Cells.xls[/URL]
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.
Yes, I believe so. If I'm understanding your statement correctly. If you are highlighting a row or column instead of a cell, once a cell is selected outside of the current range then the intersect moves to the new selected range.
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
OK, comment the code at the top and then click somewhere. You are wanting the color to stay?

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
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?
Sorry, I missed the code at the bottom. The click event will override the other Sub. If you combine the two maybe it gets you closer to what you want??? Later...


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

Set MyPage = Range("i6:af28")
For Each Cell In MyPage

If Cell.Value = "AOD" Then
Cell.Interior.ColorIndex = 36
'End If
ElseIf Cell.Value = "MISC" Then
Cell.Interior.ColorIndex = 16
'End If
ElseIf 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
With Columns(c).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With
With Rows(r).Interior
.ColorIndex = 20
.Pattern = xlSolid
End With


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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!