VBA Conditional Formatting 1

[jnp102]

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

 

[TonyJollans]

Hi Jeremy,

Just for future reference, there is a Forum dedicated to VBA (Forum707) where this sort of question would be better posed.

As it's here now, the problem with the code is that it runs whenever the Selection changes when what you want is to run whenever the data changes. There is another event (Worksheet Change) which runs when cell contents are changed. One difference between the two is that the Target Range passed to the Change event is of the changed cell(s) whilst the Target passed to the Selection Change Event is of the cell(s) you are moving to - not the one(s) changed and which you are moving from.

So to make it work you should move it to the Change Event. Assuming you have no other code in the Sheet, just change:
[tt] Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/tt]
.. to ..
[blue][tt] Private Sub Worksheet_Change(ByVal Target As Range)[/tt][/blue]

If you already have other code you may have to do it slightly differently. Post back if you have any problems with it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[jnp102]

Sorry about posting in the wrong place, I just saw the others in here with VBA and went with it. I made that small change and it works great!!!! I didn't even think about that!!!! Thank you so much Tony



Jeremy