Count cells with any data in and cahnge colour when = to specific cell value
Hi,
I am trying to get a formula that will change the cell colour when the number of cells in a column with data entered equals the value in a predetermined cell.
I.E.
Cell A1 has a value of 10 entered in, so when the number of cells between A3-A43 with data in = 10, A1 to turn Yellow.
its excel 2010.
Thanks
conditional format a1 as formula option of
=counta(A3:A43)>=a1
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi
You can use Conditional Formatting on cell A1. Select A1 then click on Conditional Formatting. Choose use a Formula (last option) and paste this formula into the box:
=COUNTA(A3:A43)>=A1
Good luck.
Tony
Re: Count cells with any data in and change colour when = to specific cell value
Hi Guys,
that worked a charm. Is there a formula that would prevent data being entered into a cell until the value in A1 is increased?
IE if A1 =15, when 15 cells have data in the same column the rest are blocked until A1's value is increased?
Brgds
GH
you could use data validation
apply validation of custom /formula
=COUNTA($A$3:$A$43)<=$A$1
to cells a3:a43
however you could still paste over the cells though
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks