Create Comparative Button

[young engineer]

Hi All

I want to create a button to compare sets of results. I want it to be so that column H is next to column U, I next to V, M next Z and Q next to AH and AL.
I also want to have columns H,I,M,Q shaded in one colour and columns U,V,Z,AL shaded in another column.
Does anyone know how to help me, please.

 

[Yard]

Test this on a sample of your data:

Sub MoveCols()
Dim lngColour1 As Long, lngColour2 As Long, wsComp As Worksheet
lngColour1 = 6
lngColour2 = 8
Set wsComp = Worksheets("Compare")
Application.ScreenUpdating = False
With wsComp
    With .Range(.Cells(1, "H"), .Cells(.Rows.Count, "H").End(xlUp))
        .Interior.ColorIndex = lngColour1
        .Copy
        wsComp.Cells(1, "V").PasteSpecial xlPasteAll
        .Interior.ColorIndex = lngColour2
    End With
       
    With .Range(.Cells(1, "I"), .Cells(.Rows.Count, "I").End(xlUp))
        .Interior.ColorIndex = lngColour1
        .Copy
        wsComp.Cells(1, "W").PasteSpecial xlPasteAll
        .Interior.ColorIndex = lngColour2
    End With
       
    With .Range(.Cells(1, "M"), .Cells(.Rows.Count, "M").End(xlUp))
        .Interior.ColorIndex = lngColour1
        .Copy
        wsComp.Cells(1, "AA").PasteSpecial xlPasteAll
        .Interior.ColorIndex = lngColour2
    End With
       
    With .Range(.Cells(1, "Q"), .Cells(.Rows.Count, "Q").End(xlUp))
        .Interior.ColorIndex = lngColour1
        .Copy
        wsComp.Cells(1, "AI").PasteSpecial xlPasteAll
        wsComp.Cells(1, "AM").PasteSpecial xlPasteAll
        .Interior.ColorIndex = lngColour2
    End With
       
       
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[young engineer]

I inserted a toggle button fom the activex controls, and inserted the code, but did not work. How should put the code in.

 

[Yard]

I don't follow what you are toggling? You said you wanted a button to move some columns?

 

[young engineer]

Yes. But I want to revert back to my original data after looking at my comparison data, so I assumed I need a toggle button to do this.
Should I just place this code in my macro.

 

[Yard]

OK, so I would leave the code I gave you in a module. Then add this code to revert to the original data:

Sub RestoreCols()
Dim wsComp As Worksheet, rngClearAll As Range, rngClearColour As Range
Set wsComp = Worksheets("Compare")
Application.ScreenUpdating = False
With wsComp
    Set rngClearAll = Union(.Range(Cells(1, "V"), .Cells(.Rows.Count, "V").End(xlUp)), _
            .Range(Cells(1, "W"), .Cells(.Rows.Count, "W").End(xlUp)), _
            .Range(Cells(1, "AA"), .Cells(.Rows.Count, "AA").End(xlUp)), _
            .Range(Cells(1, "AI"), .Cells(.Rows.Count, "AI").End(xlUp)), _
            .Range(Cells(1, "AM"), .Cells(.Rows.Count, "AM").End(xlUp)))
    rngClearAll.Clear
    
    Set rngClearColour = Union(.Range(Cells(1, "H"), .Cells(.Rows.Count, "H").End(xlUp)), _
            .Range(Cells(1, "I"), .Cells(.Rows.Count, "I").End(xlUp)), _
            .Range(Cells(1, "M"), .Cells(.Rows.Count, "M").End(xlUp)), _
            .Range(Cells(1, "Q"), .Cells(.Rows.Count, "Q").End(xlUp)))
    With rngClearColour.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
    End With
    
End With
Application.ScreenUpdating = True
End Sub

Then put this in the code for the toggle button:

Private Sub ToggleButton1_Click()
If ToggleButton1.Value Then
    Call MoveCols
    Else
    Call RestoreCols
End If
End Sub

 

[young engineer]

I did that. Getting a Runtime error 9: Subscript out of range.

When I debug, the error is in the Move Cells() Code,
Line:
Set wsComp = Worksheets("Compare")

 

[young engineer]

I did that. Getting a Runtime error 9: Subscript out of range.

When I debug, the error is in the RestoreCol() Code,
Line:
Set wsComp = Worksheets("Compare")

 

[Yard]

Just change "Compare" to the name of the worksheet you want it to work with.

(NB if the same method is to be used on several worksheets, we could modify the code so it works for any toggle button).