How do I use conditional formatting for an entire column?

  • Thread starter Thread starter Guest
  • Start date Start date
If I get you correctly, whene there was a second value(an e-mail address)
that value went into the next cell below the first data item and nothing ever
went into column B.

From the first line you could put a formula that checks for the presence of
an '@' in the cell below in column A, and if so, copy it.
=IF(ISERROR(FIND("@",A2)),"",A2) this assumes you don't have a column
header. IF so use A3. Copy it down all the way through your range. Verify
your results. Select Column B. Copy. Paste Special -- Values. Sort the
two columns on Column B's values. Delete all the rows that have an e-mail
address in column A and nothing in column B.

IMPORTANT: Of course, backup first in case I have something wrong in what
you're dealing with.
 
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...
 
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
 
Back
Top