Check Boxes that highlight entire row

[xcsnine]

I've got a friend who runs a DJ show for weddings. He wants me to create an excel sheet with about 8400 song titles, artist, album and track numbers so he can give it out for people to choose the songs they'd like played. He wants them to be able to open the excel sheet on their computer and click the CHECK box to highlight the entire row. I've set up about 20 check boxes (format control) and conditional conditional formatting (=$g2=TRUE()) to highlight the row YELLOW. Obviously 8400 songs going one by one is going to take forever and a day to do. Is there an easier way to do this? I found this event macro online from http://www.mvps.org/dmcritchie/excel/event.htm#case:

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2004-09-26, programming, Case -- Entire Row
' http://www.mvps.org/dmcritchie/excel/event.htm#case
If Target.Column <> 6 Then Exit Sub 'Column F is column 6
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Select Case LCase(Target.Value)
Case "yes"
Target.EntireRow.Interior.ColorIndex = 34
Case "no"
Target.EntireRow.Interior.ColorIndex = 36
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Application.EnableEvents = True 'should be part of Change macro
End Sub

But I'm not too familiar with VBA...

I appreciate any help anyone is able to provide in making this tedious task go a little faster. I've uploaded what I'm working with as well...

Nick

[royUK]

Checkboxes are not a good choice, see this example. Click in Column A to select/se-select

[modytrane]

try this code.
it will put an X in column A and color next seven cells on the same row.
modytrane.

Please Login or Register  to view this content.

[modytrane]

Here's your workbook with the macro installed.
I had to trim the file to make it smaller.
You can select and deselect by clicking in the A column.
modytrane

[xcsnine]

Thanks for the quick replies! Modytrane. I downloaded what you did (THANKS!) but nothing happens when i click in Column A. I have macros enabled (trusted center) and when clicking the View Macro's nothing comes up. Maybe i did something wrong?

Thanks again for the help, it'll be a HUGE time saver.

@RoyUK:
Checkboxes aren't a good idea? The Windings 2 option for the check box worked when we printed out the list but he wants an interactive excel sheet that customers can click and highlight their selections by clicking in column A. Thanks man!

[royUK]

When you click the cell in A it triggers conditional formatting in the adjacent cells.

Click a cell containing the check to clear it.

That's what you needed

[xcsnine]

Perfect thanks! That's awesome saved me ton's of time! Is there anyway to make the highlight color yellow instead of blue? I was looking at the code and didn't see a place that mentioned any colors.

Thanks again

[royUK]

If you are refeerring to my suggestion, then the lines are coloured with Conditional Formatting. Just change the Format in the Conditional Format

[modytrane]

Number 34 [highlighted in RED] below needs to be changed to the color of your choice.
Use the attached file to select the number for the color.
modytrane.

Please Login or Register  to view this content.