Using Len and Mid For condition Formatting - VBA

[KNKN9]

Hi,


I have the code

If Len((Range("I10")) = 3 And Mid((Range("I10")), 3, 1) > 5 Then
(Range("I10").Interior.ColorIndex = 3

ElseIf Len(Range("I11")) = 4 And Mid((Range("I11")), 3, 2) > 5 Then
Range("I11").Interior.ColorIndex = 3

ElseIf Len(Range("I12")) = 5 And Mid((Range("I12")), 3, 3) > 5 Then
Range("I12").Interior.ColorIndex = 3

End If

which formats the cells based on the condition stated . However I want to use a Private Sub so it changes when the instrg is entered in a cell. I also want this for a set of range and not just one cell. I.E Range ("I10:AA1000").

Thanks in advanced for your help !

 

[Joe4]

Sounds like you want a Worksheet_Change event procedure code, which is VBA code that automatically runs as data is manually entered into a specific range.
Here is code that will do that for you, but it must be placed in the correct module.
Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("I10:AA1000"))
    
    If Not rng Is Nothing Then
        For Each cell In rng
            Select Case Len(cell)
                Case 3, 4, 5
                    If Mid(cell, 3, Len(cell) - 2) > 5 Then cell.Interior.ColorIndex = 3
            End Select
        Next cell
    End If

End Sub

For more on event procedure code, see here: http://www.cpearson.com/excel/events.aspx

 

[KNKN9]

Perfect !!
This does the job !

Much Appreciated and will look more into event procedure it does really come in handy as don't need to create a command button all the time and the code could run itself !

 

[Joe4]

You are welcome!

Yes, event procedures are very handy. And on "manual change" isn't the only one. You can trigger things to run on opening the file, saving file, selecting certain cells, as well as many others.