Forum Discussion
Conditional formatting based on a checkbox
Good afternoon.
I wonder if someone can help me please :-)
I have a spreadsheet that has rows of client data, what I am trying to achieve is when I click the checkbox it turns the row green. I have researched it and all the info look different to the conditional formatting I can see, I am using Excel 365 web based excel I don't see the the option 'Use a formula to determine which cells to format.'
I see apply to: and Format Cell where the formula is true can someone please help
Thanks Joel
2 Replies
HIGriffij76,
You can definitely do this in Excel 365 (web), but the trick is that the checkbox needs to be linked to a cell first. Conditional formatting doesn’t read the checkbox directly — it reads the TRUE/FALSE value in the linked cell.
Steps:
1. Link the checkbox to a cell• Right‑click the checkbox → Format Control.
• In Cell link, choose a helper cell (e.g. Z2).
• When checked, that cell will show TRUE; when unchecked, it will show FALSE.2. Select the row you want to format• Highlight the row of client data (e.g. row 2).
3. Create the conditional formatting rule• Go to Home → Conditional Formatting → New Rule.
• Choose “Format cells where this formula is true.”
• Enter the formula:
=$Z2=TRUE
(adjust Z2 to match your linked cell).4. Set the format• Click Format → Fill → Green.
• Save the rule.Now, whenever you tick the checkbox, the linked cell becomes TRUE and the whole row will turn green. Untick it, and the row goes back to normal.
In my online / web interface here is my Conditional Formatting (add new rule) window:
notice the Fx all the way to the right, that is the use a custom formula.
then you just need to use a formula like
=$A1
where the $A needs to be the column where the checkbox is, and
the 1 needs to match the first row in the "Apply to:" range
