How do I use conditional formatting for an entire column?

M

MikeBHog

On my fifth day of trying to get Conditional Formatting to work for me. I'm
almost convinced that I'll have to enter each Conditional Formatting Rule
individually in each cell unless someone else has found a work around.

I have two columns of numbers B and C.
I'm trying to apply CF to cells in column B.
I'm applying two rules to each cell.
I'm starting by applying the rules to a single cell (B2) and then want to
copy to the rest of the rows in column B.
CF Rule 1:
Use a formula to determine which cells to format: Format values where this
formula is true: =B=0
The format used is white text so that the cell value is 'hidden'.
CF Rule 2:
Graded Colour Scale: Format all cells based on their values - Format Style:
3-Color Scale
Minimum - Type:Formula; Value: =$C$2*0.5; Color: Green.
Midpoint - Type: Formula; Value: =$C$2; Color: White.
Maximum - Type:Formula; Value: =$C$2*1.5; Color: Red.

This works as I expect and want in cell B2.
But I want it to work in the rest of the rows in column B also.
If I copy and paste special 'Formats' all the absolute references stay and
each row looks to cell C2.
If I try and edit the absolute reference in B2, even just to remove the
absolute reference to Row2 i.e $C2 I get a dialog box that says "You cannot
use relative references in Conditional Formatting criteria for color scales,
data bars, and icon sets."

My question is: Is there any way to avoid having to set the CF for each
cell/row in column B individually?

The spreadsheet is an exported file of Budgets Actual(ColumnB) V's
Budget(ColumnC) and that is just for a single month. Columns F and G are the
following month etc...
 
V

vemory

first select the whole range you want the conditional format to apply to not
just first cell - when you do the formula for first cell take $ off and check
the stop when - you won't need to copy the format - it will be automatic

so, for example, my conditional format looks like this
Cell value <>D$14 applieds to $D$18:$0$18 check the stop if true box

was driving me crazy, until I quit trying to COPY the format like in old
excel and used the applies to box to tell it the range to apply to and took $
off my $d$14 not the range
 
Top