Keep only data that is highlighted

[Serafin54]

Hi,

Is there a way via VBA to select a range of columns in a worksheet and then clear out any cell that isn't highlighted so I'm not going column by column to filter out cells with no fill and doing it manually? The columns that are needed to be cleared out may differ time and again so i can't say that the range will always be (B:M).

I was trying a couple codes i found online and tweak them but I'm heading down a rabbit hole quickly and record macro is really bulky and not forgiving.

• On this example for example, I want to be able to select the columns i want to run the code or the ones not to include. either or but thinking selecting which to include is easiest for coding. it will not always just be omitting column A. It could be A-D being omitted or etc.
• Have the code ignore the header row
• Clear the cells of any that are not highlighted in any way.

I am able to accomplish it via find/replace but just having a macro power tool at the ready to do all the steps needed would save time.

Thanks!!!!!

before:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	East Color	East Units	East Sales	North Color	North Units	North Sales	South Color	South Units	South Sales	West Color	West Units	West Sales
2	01/03/2016	x	x	x	x	x	x	x	x	x	1	1	11
3	01/13/2016	x	x	x	x	x	x	1	8	96	x	x	x
4	01/21/2016	x	x	x	x	x	x	x	x	x	1	2	26
5	01/30/2016	x	x	x	1	7	84	x	x	x	x	x	x
6	02/07/2016	x	x	x	1	8	104	x	x	x	x	x	x
7	02/13/2016	x	x	x	x	x	x	1	2	22	x	x	x
8	02/21/2016	1	5	60	x	x	x	x	x	x	x	x	x
9	03/01/2016	x	x	x	x	x	x	x	x	x	1	2	26
10	03/13/2016	1	8	96	x	x	x	x	x	x	x	x	x
11	03/23/2016	x	x	x	1	7	84	x	x	x	x	x	x
12	03/28/2016	x	x	x	x	x	x	x	x	x	1	2	26
13	04/03/2016	x	x	x	x	x	x	1	8	96	x	x	x
14	04/12/2016	x	x	x	x	x	x	1	1	13	x	x	x
15	04/16/2016	1	8	88	x	x	x	x	x	x	x	x	x
16	04/23/2016	x	x	x	x	x	x	x	x	x	1	6	66
17	04/30/2016	x	x	x	x	x	x	1	5	65	x	x	x
18	05/09/2016	x	x	x	x	x	x	1	7	84	x	x	x
19	05/16/2016	x	x	x	x	x	x	1	5	55	x	x	x
20	05/25/2016	x	x	x	x	x	x	1	1	12	x	x	x
21	05/30/2016	x	x	x	1	4	48	x	x	x	x	x	x
22	06/04/2016	x	x	x	1	7	84	x	x	x	x	x	x
23	06/13/2016	1	3	33	x	x	x	x	x	x	x	x	x
24	06/21/2016	x	x	x	x	x	x	1	2	24	x	x	x
25	06/26/2016	x	x	x	x	x	x	1	6	72	x	x	x
26	07/02/2016	1	6	66	x	x	x	x	x	x	x	x	x
27	07/08/2016	x	x	x	x	x	x	x	x	x	1	2	26
28	07/12/2016	x	x	x	x	x	x	1	4	48	x	x	x
29	07/19/2016	x	x	x	x	x	x	1	1	13	x	x	x
30	07/26/2016	x	x	x	x	x	x	x	x	x	1	1	12
31	07/31/2016	x	x	x	x	x	x	1	2	22	x	x	x
32	08/07/2016	x	x	x	x	x	x	x	x	x	1	5	65
33	08/13/2016	x	x	x	x	x	x	x	x	x	1	5	60
34	08/20/2016	x	x	x	1	8	104	x	x	x	x	x	x
35	08/25/2016	x	x	x	x	x	x	x	x	x	1	4	48
36	09/01/2016	x	x	x	x	x	x	1	6	66	x	x	x
37	09/07/2016	x	x	x	x	x	x	1	7	91	x	x	x
38	09/10/2016	x	x	x	1	4	44	x	x	x	x	x	x
Original

After:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Date	East Color	East Units	East Sales	North Color	North Units	North Sales	South Color	South Units	South Sales	West Color	West Units	West Sales
2	01/03/2016										1	1	11
3	01/13/2016							1
4	01/21/2016										1
5	01/30/2016				1
6	02/07/2016				1		104
7	02/13/2016							1
8	02/21/2016	1
9	03/01/2016										1
10	03/13/2016	1
11	03/23/2016				1
12	03/28/2016										1
13	04/03/2016							1
14	04/12/2016							1	1	13
15	04/16/2016	1
16	04/23/2016										1
17	04/30/2016							1
18	05/09/2016							1
19	05/16/2016							1
20	05/25/2016							1	1	12
21	05/30/2016				1
22	06/04/2016				1
23	06/13/2016	1
24	06/21/2016							1
25	06/26/2016							1
26	07/02/2016	1
27	07/08/2016										1
28	07/12/2016							1
29	07/19/2016							1	1	13
30	07/26/2016										1	1	12
31	07/31/2016							1
32	08/07/2016										1
33	08/13/2016										1
34	08/20/2016				1		104
35	08/25/2016										1
36	09/01/2016							1
37	09/07/2016							1		91
38	09/10/2016				1
Results

 

[gordsky]

how are you coloring the cells? Is it through conditional formatting, a macro or using the fill for each one?

 

[Serafin54]

it is via a macro that compares the two files.

 

[gordsky]

it is via a macro that compares the two files.

Does the macro set an "interior Colour" or a "Conditional Format". VBA recognizes both differently so dependent on it is set will depend how it will need to search

 

[Serafin54]

from what i can gather it is an interior/fill. I am seeing this code and can't find anything to point me towards conditional formatting.

With .Settings

  .Highlight = HighlightType_Standard

End With

 

[gordsky]

are you able to post the full code

 

[Serafin54]

Unfortunately not. The work to get the files color coded comes from an auditing software. I just pulled that much from the dev section glossary.

 

[Joe4]

Try placing your cursor on one of the colored cells and running the following VBA code:

Sub ColorTest()

    Dim cell As Range
    
    If ActiveCell.Interior.ColorIndex <> -4142 Then
        MsgBox "ActiveCell colored manually"
    Else
        If ActiveCell.DisplayFormat.Interior.ColorIndex <> -4142 Then
            MsgBox "ActiveCell colored with Conditional Formatting"
        Else
            MsgBox "ActiveCell not colored at all"
        End If
    End If
    
End Sub

This will tell you if your code is being colored via Conditional Formatting or manually (which may also be by VBA code, but not Conditional Formatting).

 

[Serafin54]

Try placing your cursor on one of the colored cells and running the following VBA code:

Sub ColorTest()

    Dim cell As Range
   
    If ActiveCell.Interior.ColorIndex <> -4142 Then
        MsgBox "ActiveCell colored manually"
    Else
        If ActiveCell.DisplayFormat.Interior.ColorIndex <> -4142 Then
            MsgBox "ActiveCell colored with Conditional Formatting"
        Else
            MsgBox "ActiveCell not colored at all"
        End If
    End If
   
End Sub

This will tell you if your code is being colored via Conditional Formatting or manually (which may also be by VBA code, but not Conditional Formatting).

ActiveCell colored manually

 

[gordsky]

You could try something like this. If you select a random range it will apply the macro to that range. If you select entire columns then rather than using the full row count it will find the last used row of the selection. This is simply to speed up the code slightly.

Sub ClearCel()

Dim ws As Worksheet
Dim MyRng As range, cel As range
Dim lrow As Long, lcol As Long

Set ws = ActiveSheet

    If Selection.Rows.Count = ws.Rows.Count Then
        lrow = Selection.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lcol = Selection.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        Set MyRng = ws.range(ws.Cells(2, Selection.Column), ws.Cells(lrow, lcol))
        
    Else
        Set MyRng = Selection
        
    End If

    For Each cel In MyRng

        If cel.Interior.ColorIndex = xlNone Then
            cel.ClearContents
        
        Else
        
        End If

    Next cel

End Sub