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]

@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

 

[dermie_72]

Hi Yamasaki,
What have you tried so far?
See if you can create a macro that:
Sets a range, Ideally either a selection, or a hard coded range.
Then for each cell in the range check to see if the interior color is red.
if it is clearcontents.
if not move on.

 

[Yamasaki450]

Hi Yamasaki,
What have you tried so far?
See if you can create a macro that:
Sets a range, Ideally either a selection, or a hard coded range.
Then for each cell in the range check to see if the interior color is red.
if it is clearcontents.
if not move on.

Hey. Thanks but i have no clue how to write macro code heh Thats why im asking here if someone is willing to write it.

 

[Rick Rothstein]

Does this macro do what you want (it physically clears the cells that are shown in red)...

Sub ClearRedCells()
  Dim Cell As Range
  Application.ScreenUpdating = False
  For Each Cell In Range("F3:L23")
    If Cell.DisplayFormat.Interior.Color = vbRed Then
      Cell.Clear
    End If
  Next
  Application.ScreenUpdating = True
End Sub

 

[Akuini]

If this is a follow-up question for the thread titled "vba-macro-to-delete-highlighted-cells," please provide the link to the thread so that everyone who reads this can understand the context. An important detail from the previous thread is that you are working with over 14 million cells.

Cells are highlighted with conditional formatting.

What is formula of the conditional formatting?

 

[Yamasaki450]

Does this macro do what you want (it physically clears the cells that are shown in red)...

This does what i want but its too slow even for one row of data (1200 cells).

 

[Yamasaki450]

If this is a follow-up question for the thread titled "vba-macro-to-delete-highlighted-cells," please provide the link to the thread so that everyone who reads this can understand the context. An important detail from the previous thread is that you are working with over 14 million cells.



What is formula of the conditional formatting?

I used this formulas to give cells color "=AND($C2=3,D2<=-4,D2>=-65)"

Post #2 in this thread.

Conditional formatting formula

Hey everyone i need some help with formulas. I need conditional formatting formula to highlight specific range of numbers in specific row. I would like to highlight all numbers from (-4 to 65) in rows where number 3 is in column C. So in this case all numbers from (-4 to 65) in range E4:T4 and...

www.mrexcel.com

 

[Akuini]

I used this formulas to give cells color "=AND($C2=3,D2<=-4,D2>=-65)"

But in your example in post #1 there are no data in col C, why?

 

[Yamasaki450]

But in your example in post #1 there are no data in col C, why?

I didnt use formula in post #1. I did it manually with conditional formatting rule just to show what i want... I didnt know that formula is important... Here is example with formula.