VBA to remove highlighted cells

[Yamasaki450]

Hello again.

I would kindly ask for another VBA to remove red highlighted cells. Cells are highlighted with conditional formatting.

Thanks guys.

 

[Akuini]

edit: wrong code

 

[Akuini]

@Yamasaki450
1. Please, remove the conditional formatting first
2. Run this code (you need to adjust the range):

Sub Yamasaki450_10()
'=AND($C2=3,D2<=-4,D2>=-65)"
Dim c As Range
Dim i As Long, j As Long
Dim t As Double
t = Timer
'change the range to suit
Set c = Range("D2:E12")
va = c
vb = Range("C2:C12")

For i = 1 To UBound(vb, 1)
    If vb(i, 1) = 3 Then
        For j = 1 To UBound(va, 2)
            If va(i, j) <= -4 Then
                If va(i, j) >= -65 Then
                    va(i, j) = ""
                End If
            End If
        Next
    End If
Next

c = va
Debug.Print "Completion time:  " & Format(Timer - t, "0.00") & " seconds"
End Sub

Example:
Before:

Book2
	C	D	E
1
2	1	65	3
3	3	-5	-64
4	64	-100	-64
5	2	1	2
6	3	-5	-66
7	3	3	-3
8	3	-4	100
9	3	-5	-65
10	1	-100	-5
11	65	1	65
12	-5	-5	10
13
Sheet1

After:

Book2
	C	D	E
1
2	1	65	3
3	3
4	64	-100	-64
5	2	1	2
6	3		-66
7	3	3	-3
8	3		100
9	3
10	1	-100	-5
11	65	1	65
12	-5	-5	10
13
Sheet1

 

[Yamasaki450]

@Yamasaki450
1. Please, remove the conditional formatting first
2. Run this code (you need to adjust the range):

Nice. This works very fast now... I didnt realize this can be done without conditional formatting. Big thanks Akuini for your time and effort... And also thanks to all others for helping me out.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.