Forum Discussion
Solved
Formula for Conditional formatting of table columns
Good evening.
I have a large table of 1000 rows (1-1000) and 1000 columns ("A"-"ALL")
In each column there are integer values for each cell.
I should highlight in Green all the columns that do not have any cells containing the value 0.
I can't find the formula and I don't know if it exists.
Thank you for your help.
You hadn't told us that you're using the Italian version of Excel. Use this formula:
=CONTA.SE(A$1:A$1000; 0)=0
Select A1:ALL1000. A1 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=COUNTIF(A$1:A$1000, 0)=0
Click Format...
Activate the Fill tab.
Select green as highlight color.
Click OK, then click OK again.Hi Hans and thanks for the reply.
That's exactly what I have to do!
Highlight all columns that do not contain a 0 in green.
The formula is interpreted by Excel as incorrect, whether I select the cells from A1 to ALL1000, or whether I select them as columns from A to ALL, or whether I select only the cells of row 1.
Maybe I'm doing something wrong?
I attach the screenshots.
They are in Italian but I think you understand the meaning.Thank you
You hadn't told us that you're using the Italian version of Excel. Use this formula:
=CONTA.SE(A$1:A$1000; 0)=0
- much better than what I said!
First let's be very clear on what you mean by
I should highlight in Green all the columns that do not have any cells containing the value 0.
Do you mean, as the words seem to be saying (but I'm turning the negative into a positive) that the entire column is to be highlighted in green if every cell in that column contains an integer value other than zero.
Assuming that's the case, an easy way to do it--though not with a single formula--would be to use 1,000 helper columns, one for each of the subject/target columns, and have a formula in each cell of the helper column to the effect, =IF(A2<>0,1,0), and then, use a total at the top or bottom of that column to serve as the basis for the conditional formatting of column A. And so forth.
If you have a current version of Excel, it's also possible that a LET function could be devised that would do all of this in a single formula, but let's hold off on that for now.
