transferring conditional format into VBA code

[Vangaard]

How can I tranfer the following conditional format into VBA code?

=IF(G4<>"",IF(LEFT(G3,11)=LEFT(G4,11),1,0),"")

I would also like to have this looped to check the condition for the range of G3:G253.

For the condition where the last 2 digits of G4 are greater than G3, then I"d like to delete G3, etc.

again, any assitance would be greatly appriciated.

 

[Andrew Poulsom]

Have you tried recording a macro while entering the CF manually?

 

[Vangaard]

I have tried to run this in a macro. It will record it, but only as keystroke type session.
I can have it autofill for the range that I need, and even format it to change color/font etc.

I'm trying to be able to delete specific cells if the condion is met. Currently, it will only highlight a cell when condition is met. I would also need to do this for the entire range

 

[Vangaard]

I'm still struggling to resolve this issue.
See my previous post for conditional format that highlights the correct cells. That formula works, but I need to be able to delete the identified cells, not just highlight them, and conditional format does not allow for that. At least not that I'm aware of.

Sample data is:
A1 0435225397100
A2 0435225397100
A3 0503820711100
A4 0503820711101

If the first 11 characters of A1 and A2 are the same, then need to compare the last 2. If the last 2 are the same, then A2 gets deleted. A1 is different from A3 so no reaction. If the last 2 characters in A4 is one higher than A3, then delete A3. Make sense?

Below is what I have so far, but my IF statement is not producing the results I need.

Sub row_delete()

Dim i As Integer
Dim myrange As Range
Dim Sh As Worksheet


Set Sh = Worksheets("Sheet1")
Set myrange = Sh.Range("G3:G" & Sh.Range("G253").End(xlUp).Row)


For i = 1 To myrange.Rows.Count Step 1 'set number of instances
If Left("G" & (i + 3), 11) = Left("G" & (i + 2), 11) Then
' Selection.ActiveCell.Delete Shift:=xlUp
End If

Next i


End Sub

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim r As Long
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("G3:G" & Sh.Range("G65536").End(xlUp).Row)
    For r = Rng.Rows.Count To 2 Step -1
        With Rng.Cells(r, 1)
            If Left(.Value, 11) = Left(.Offset(-1, 0).Value, 11) Then
                .EntireRow.Delete
            End If
        End With
    Next r
End Sub

 

[Vangaard]

Thanks very much!

While this doesn't do exactly what I had originally intended (deleting row instead of single cell), I am able to take what you have supplied and use it to get the results that I was looking for.

I really do appriciate the help on this one!