Create Comparative Button

Y

young engineer

Board Regular
Joined
Mar 3, 2009
Messages
100
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Test this on a sample of your data:
Code: 
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
 
Upvote 0
I inserted a toggle button fom the activex controls, and inserted the code, but did not work. How should put the code in.
 
Upvote 0
I don't follow what you are toggling? You said you wanted a button to move some columns?
 
Upvote 0
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.
 
Upvote 0
OK, so I would leave the code I gave you in a module. Then add this code to revert to the original data:

Code: 
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:

Code: 
Private Sub ToggleButton1_Click()
If ToggleButton1.Value Then
    Call MoveCols
    Else
    Call RestoreCols
End If
End Sub
 
Upvote 0
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")
 
Upvote 0
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")
 
Upvote 0
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).
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
conditional formatting question
Replies
2
Views
151
kerm007
K
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
149
gtd526
gtd526
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
119
gtd526
gtd526
gtd526
  • Question
macro to add correct number of ")" at end of nested IF statement
Replies
1
Views
117
gtd526
gtd526
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
259
Gajendran Yadhav
G

Forum statistics

Threads
1,214,622
Messages
6,120,585
Members
448,972
Latest member
Shantanu2024

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top