Remove data which isn't highlighted and shift it all to the left.

[chanman]

Hi - Some help required please!

Anyone able to help me with the follow?

Book1
	A	B	C	D	E
1	Sample1	Sample2	Sample3	Sample4	Sample5
2	3	A	2	B	G
3	1	3	2	1	1
4	1	A	3	B	3
5	1	4	2	3	1
6	3	3	2	1	3
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:E6	Cell Value	=3	text	NO

Required output;

Book1
	A	B	C	D	E
1	Sample1	Sample2	Sample3	Sample4	Sample5
2	3
3	3
4	3	3
5	3
6	3	3	3
Sheet2

thanks in advance!

 

[Peter_SSs]

I don't understand how that can be when in the first post your cf condition is cell equals 3. Oh well.

Perhaps the thread title gives the clue?

 

[Rick Rothstein]

@Rick Rothstein - fyi your code left me with an empty sheet

Sorry, I just noticed your original post indicated your cells are colored by conditional formatting... my code assumes they were manually colored.

 

[Micron]

Perhaps the thread title gives the clue?

Look at the "required output". Where is the G that magically shows up in post 5 and ends up being the accepted solution?
- I see only 3's in the "required output".
- The cf formula is only for 3. I'd say that an sample of what you want trumps anything you might write that describes it. You, sir, are clairvoyant for sure!

 

[chanman]

Thanks for the feedback chanman
Although you have a solution, I always feel compelled to correct any previously shared code now new conditions are known. So just for my own benefit:

Option Explicit
Sub chanman_V3()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")       '<-- *** Change to actual sheet name ***
    Dim r As Range, c As Range
    For Each c In ws.Range("A2", ws.Cells(Rows.Count, "E").End(xlUp))
        If c.DisplayFormat.Interior.Color <> vbYellow Then
            If r Is Nothing Then Set r = c Else Set r = Union(r, c)
        End If
    Next c
    r.Delete shift:=xlToLeft
    Application.ScreenUpdating = True
End Sub

@kevin9999 appreciate the effort so I had to give a test of course. If worked, but only partially. It only performed the deletion on row 2..

 

[kevin9999]

No worries, looks like you got a good solution from Peter anyway

 

[chanman]

I don't understand how that can be when in the first post your cf condition is cell equals 3. Oh well.

@Micron like I said in the my post yesterday, I over simplified my conditional formatting when creating the sample data. Sorry again!