transferring conditional format into VBA code

Vangaard

New Member
Joined
Oct 19, 2005
Messages
35
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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? :confused:

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
 
Upvote 0
Try this:

Code:
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,211,960
Messages
6,105,081
Members
447,947
Latest member
OX_2005

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top