Hey everyone! Got a quick question about VBA Conditional Formatting. With help from other posts, specifically from xlbo, I put the following into the worksheet....
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("C8:T145")) Is Nothing Then
For Each oCell In Target
Select Case Left(oCell.Value, 1)
Case Is = "S"
oCell.Font.ColorIndex = 2
oCell.Interior.ColorIndex = 9
Case Is = "V"
oCell.Font.ColorIndex = 2
oCell.Interior.ColorIndex = 10
Case Is = "P"
oCell.Font.ColorIndex = 1
oCell.Interior.ColorIndex = 27
Case Is = "O"
oCell.Font.ColorIndex = 1
oCell.Interior.ColorIndex = 39
Case Is = "T"
oCell.Font.ColorIndex = 1
oCell.Interior.ColorIndex = 45
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End If
End Sub
This works great except for one minor problem. Say if I were to type V2 into Cell C8, according to the code above it should make that cell green with white text....and it does, but not until I move to another cell and then back to the original Cell.
So for example (just for clarification), I type V2 into CELL C8 the cell color and text color stay default but when I click on...say CELL C9 and then click on CELL C8 again (without typing anything in) it changes the color to the correct color according to the VB above.
I hope that I haven't confused anyone but I would like to know if this can be fixed or what causes this to happen...etc.
Thanks Everyone
Jeremy
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Target, Range("C8:T145")) Is Nothing Then
For Each oCell In Target
Select Case Left(oCell.Value, 1)
Case Is = "S"
oCell.Font.ColorIndex = 2
oCell.Interior.ColorIndex = 9
Case Is = "V"
oCell.Font.ColorIndex = 2
oCell.Interior.ColorIndex = 10
Case Is = "P"
oCell.Font.ColorIndex = 1
oCell.Interior.ColorIndex = 27
Case Is = "O"
oCell.Font.ColorIndex = 1
oCell.Interior.ColorIndex = 39
Case Is = "T"
oCell.Font.ColorIndex = 1
oCell.Interior.ColorIndex = 45
Case Else
oCell.Interior.ColorIndex = xlNone
End Select
Next oCell
End If
End Sub
This works great except for one minor problem. Say if I were to type V2 into Cell C8, according to the code above it should make that cell green with white text....and it does, but not until I move to another cell and then back to the original Cell.
So for example (just for clarification), I type V2 into CELL C8 the cell color and text color stay default but when I click on...say CELL C9 and then click on CELL C8 again (without typing anything in) it changes the color to the correct color according to the VB above.
I hope that I haven't confused anyone but I would like to know if this can be fixed or what causes this to happen...etc.
Thanks Everyone
Jeremy