How do you set a column or row to always be a certain color even when adding rows or columns? If you shade/color a column normally, when you add a row or copy paste another cell into that column it will be with no fill or with the fill of the copied cell, respectively. I want column A to always be blue no matter what. Can this be achieved?
Excel – How to permanently color a column in Excel
microsoft excelmicrosoft-excel-2010
Related Solutions
Here is an answer with two helper columns (of course you can hide them):
- helper1:
=AGGREGATE(2,5,A2)- it just shows 1 for visible and 0 for invisible rows (of course you always see 1 :) )
- helper2:
=IF(C2=1,IFERROR(MAX($D$1:D1)+(COUNTIFS($A$1:A1,A2,$C$1:C1,1)=0),1),"")MAX($D$1:D1)- looks for greatest group number so farCOUNTIFS($A$1:A1,A2,$C$1:C1,1)- checks whether current value is present in ABOVE VISIBLE rowsMAX(...)+(COUNTIFS(...)=0)- increases group number if it's a new groupIFERROR(...,1)- sets group number to 1 for first visible rowIF(C2=1,...,"")- calculates group number only for visible rows
Setting up conditional formatting:
- go to: Home - conditional formatting - new rule - use a formula...
- in formula enter
=MOD($D1,2)=1 - set your desired formatting
Best Answer
You could try conditional formatting:
Select entire sheet.
Conditional Formatting -> Add New Rule -> Use a formula to determine which cells to format
Enter formula:
or just
(both the same result - just depends which syntax you feel more comfortable with)
This will check for each cell (you put A1 in because you put it in for the top-left cell of the selection, so A1 because you selected everything - it'll work itself out for the rest of the sheet accordingly)... ...whether its column number is 1 - which will only be the case for column A.
Then pick the formatting you want and click OK, and you're done.