Highlight row based on checkbox with and without VBA code

[santaclaus]

Hi,

I've created excel file with old movies (from TSPDT site) and I've added checkbox column. I would like the row from cell A to cell H to highlight green if the checkbox is checked, and to highlight red if it's unchecked.

I've tried rightclick checkbox, then format control, and then link the checkbox with other cell to return true or false and base on that of course I can use conditional formatting. But I would have to do this cell by cell (macro didn't work, copy-paste also didn't work), I have 1000 rows, so maybe there is another way. I've also copied VBA codes from internet - no luck

Could you please advise how to highlight cells from A to H based on checkbox status - without VBA code, and second option - using VBA code.

I will be very grateful for your help (it will be easier for me to see which movies I should watch)

Many thanks,
Santa

 

[Jeffrey Lopez]

So without vba which would be the easier way to do it... I would do something like this:

First, you will have to link the checkboxes with a cell so that it will produce a usable value to check against... Then we can use conditional formats.

Select your entire range, and click on Conditional Formats > New Rule > Formula and use something along the lines of:

=IF($J2=TRUE,TRUE,FALSE)

Then just format that cells, how you see fit, you can use both TRUE and FALSE to do both red and green... I hope this helps.

The reason I would stay away from VBA is because you would need to do a check for each checkbox... just easier to do it this way..

(Sorry for the poor quality of my gif, system is acting crazy today)

 

[santaclaus]

Many thanks for your answer, I have one more question.

What is very important for me - could you please write is there a way to do this: First, you will have to link the checkboxes with a cell so that it will produce a usable value to check against in other way than doing it cell by cell (I guess it's impossible to copy down - it won't work).

Best,
Santa

 

[scott_n_phnx]

To add checkboxes and the linked cells, try this one. It will create checkboxes in A1 through A10 and link to cells in Column F. To change the linked cell, change 5 in the marked line to whatever you need.

Sub AddCheckBox()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("A1:A10")
  With ActiveSheet.CheckBoxes.Add(cell.Left, _
     cell.Top, cell.Width = 15, cell.Height = 12)
     .LinkedCell = cell.Offset(, 5).Address(External:=False) 'This adds the True/False into ColumnF.  Modify as needed
     .Interior.ColorIndex = xlNone
     .Caption = ""
    End With
Next

With Range("A1:A10")
.Rows.RowHeight = 16
End With

Range("A1").EntireColumn.Select
    Selection.ColumnWidth = 5#
Range("B1").Select
Application.ScreenUpdating = True

End Sub

 

[santaclaus]

Great, it works perfectly now!

Many thanks scott_n_phnx , Many thanks Jeffrey Lopez

Best,
Santa

 

[qaharoon]

So without vba which would be the easier way to do it... I would do something like this:

First, you will have to link the checkboxes with a cell so that it will produce a usable value to check against... Then we can use conditional formats.

Select your entire range, and click on Conditional Formats > New Rule > Formula and use something along the lines of:

=IF($J2=TRUE,TRUE,FALSE)

Then just format that cells, how you see fit, you can use both TRUE and FALSE to do both red and green... I hope this helps.

The reason I would stay away from VBA is because you would need to do a check for each checkbox... just easier to do it this way..

(Sorry for the poor quality of my gif, system is acting crazy today)

Hello, Thank you for the guidance, I am a new excel user and followed your steps and I am having one issue.

I need a large number of check boxes and if I drag them to multiply them they end up working for just one row.

1. I create a check box and enter the formula as you have shown.

2. I drag them to the bottom of the list (or double click on the plus sign)

3. issue: if i click on any check box they end up turning the first row green (i.e. all checkboxes are linked to only the first True/False cell)

hope I am getting my point across.

Regards

QH