Conditional formatting checkboxes multiply

rookiee

New Member
Joined
Apr 13, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi guys!

Having trouble to figure it out how to quickly multiply checboxes who have specific conditional formatting and related cell. I made two checkboxes, first one makes certain cells in a row to color in red, then another checkbox which makes them green. Like is first one is checked job is not done yet, is I thick second one it becomes green as job done.

Now, if I just go to first cell with checkbox and pull it to multiple cells I get multiple checkboxes but they are linked to same cells in first row. What I would like to have checkbox in row 3 linked to cells in same row and not to row 2 from which is "original" checkbox...I hope you understand what I would like to do. Because I need 500 more checkboxes in colomn B and another 500 in colomn C and if I need to do it manully I need to quit my regular job. :P
 

Attachments

  • Capture11.JPG
    Capture11.JPG
    126.1 KB · Views: 12

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,151
Office Version
  1. 2016
Platform
  1. Windows
Try This
VBA Code:
Sub CreateCheckBox()

Dim i As Long, lngStart As Long, lngTotal As Long
Dim strCol As String, strLink As String

strCol = InputBox("Enter Column", "ENTER CHECKBOX COLUMN")
strLink = InputBox("Enter Link Column", "ENTER LINK COLUMN")
lngStart = InputBox("Enter Starting Row", "ENTER CHECKBOX START LOCATION")
lngTotal = InputBox("How many CheckBox", "ENTER TOTAL CHECKBOX")

For i = lngStart To lngStart + lngTotal - 1
    ActiveSheet.CheckBoxes.Add(Range(strCol & i).Left, Range(strCol & i).Top, 72, 17.25).Select
    With Selection
        .Caption = ""
        .Value = xlOff '
        .LinkedCell = strLink & i
        .Display3DShading = False
    End With
Next

End Sub
 

rookiee

New Member
Joined
Apr 13, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Ok great, that worked. However I have second thing to do, can I somehow insert formula for those created checkboxes to "copy" or make certain conditional formatting? I want certain cells in a row to get color when checkbox is TRUE....I did it for first row but want to "multiply" that across all checkboxes...
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,151
Office Version
  1. 2016
Platform
  1. Windows
Ok great, that worked. However I have second thing to do, can I somehow insert formula for those created checkboxes to "copy" or make certain conditional formatting? I want certain cells in a row to get color when checkbox is TRUE....I did it for first row but want to "multiply" that across all checkboxes...
I'm now at home getting ready to prepare dinner 😁

I think you can get what you want here
 

Watch MrExcel Video

Forum statistics

Threads
1,129,517
Messages
5,636,804
Members
416,941
Latest member
shazzaxyz

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
Top