conditional formatting with letters and numbers

[bta]

Hi Gurus,
I found out about conditional formatting for five different letters or letter combinations, using VBA, using the code below.
I was wondering if this is also possible with letters or letter combinations, BV, RV, SV, CV, Z and numbers, somewhere between 0 and 9,5.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
        Select Case oCell.Value
              Case Is = "BV"
                 oCell.Interior.ColorIndex = 4
             Case Is = "RV"
                 oCell.Interior.ColorIndex = 2
             Case Is = "SV"
                 oCell.Interior.ColorIndex = 2
             Case Is = "CV"
                 oCell.Interior.ColorIndex = 2
             Case Is = "ZV"
                 oCell.Interior.ColorIndex = 2
              
         End Select
    Next oCell
End Sub

I hope you all understand what I mean.
Thanks for your time.
Greetings, Bengtar

[royUK]

Do you mean that the numbers aren't always the same?

[bta]

correct. Most of the times round numbers, sometimes not, can go up to 9,5.

[Leith Ross]

Hello bta,

I assume the numbers are not combined with the letters. If you do have letters and numbers together, where the numbers vary between 0 and 9.5, then a different macro is needed.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
        Select Case oCell.Value
              Case Is = "BV"
                 oCell.Interior.ColorIndex = 4
             Case Is = "RV", "SV", "CV", "ZV"
                 oCell.Interior.ColorIndex = 2
             Case 0 To 9.5                        'Change the period to comma
                 oCell.Interior.ColorIndex = 6  'Change this color to whatever you are using
         End Select
    Next oCell
End Sub

[bta]

Thank you Leith,
Unfortunately for some reason if I delete the contents of a cell, the cell remains yellow if I delete a letter combination or turns yellow if i delete a number.
Any idea?
Thanks for your time, Bengtar

[Leith Ross]

Hello bta,

It is happening because those conditions equate to zero. The final Case statement tests for zero and the true condition colors the cell yellow. He final compare need to be changed...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
        Select Case oCell.Value
              Case Is = "BV"
                 oCell.Interior.ColorIndex = 4
             Case Is = "RV", "SV", "CV", "ZV"
                 oCell.Interior.ColorIndex = 2
             Case Is > 0  
                 If oCell.Value < 9,5 Then                       
                    oCell.Interior.ColorIndex = 6 
                 End If
         End Select
    Next oCell
End Sub

[royUK]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
For Each oCell In Target
        Select Case oCell.Value
              Case Is = "BV"
                 oCell.Interior.ColorIndex = 4
             Case Is = "RV", "SV", "CV", "ZV"
                 oCell.Interior.ColorIndex = 2
             Case 0 To 9.5                        'Change the period to comma
                 oCell.Interior.ColorIndex = 6  'Change this color to whatever you are using
             Case Else
                 oCell.Interior.ColorIndex = xlNone
End Select
    Next oCell
End Sub

[royUK]

Can you attach the workbook.

[bta]

Can you attach the workbook.

BTA-condform-v1.zip

vba-password is bta

[Leith Ross]

Hello bta,

There is problem with the file. When I try to open it, WinZip says the file is not a valid archive. Can you can zip it again and repost the file? Thanks.

[bta]

Ok, retry please.
BTA-condform-v1.zip

[bta]

Anybody give it a try?