Related:
- Vba conditional formatting
- Vba case like - Guide
- Number to words in excel without vba - Guide
- Enable vba in excel - Guide
- How to clear formatting in excel - Guide
- Excel conditional formatting if another cell contains specific text - Excel Forum
5 responses
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'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.
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
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
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?
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
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
Didn't find the answer you are looking for?
Ask a question
Mar 2, 2009 at 07:06 PM
[URL=http://www.4shared.com/file/90418748/420881a4/Color_Cells.html]Color Cells.xls[/URL]