How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.

Yes but you do it by first selecting the column, then applying the format on
the active cell

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Patti F" wrote in message
...
How can I format an entire column using conditional format? I keep getting
an
error message when I choose the column.

You'll need to provide more information. When you say you're getting an
error message, does this mean that you aren't even able to select the column?
Or is the error coming from your Conditional Formatting formula? What is
the error message? It would also be helpful if you provided an example of
how you want your formatting to appear, and what conditions should trigger it.

"Patti F" wrote:

How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.

I'm new to Excel 2007. Trying to apply conditional formatting based on a
formula to a whole column. In the 2003 version, if I used absolute
column/relative row references (e.g., "$c1=42"), the conditional formatting
automatically adjusted the row references such that in the example above it
would go $c1, $c2, $c3, etc...

But, in this version if I select a column and use a relative reference, it
only works in the first row; and if I check the rule for just one cell with
in that column it shows the same reference as in the first row (i.e., $c1),
and it's the same in every row for the condition. Yet, the conditional
formatting help subject says explicitly in a note that relative references
are adjusted for the selected range.

Is this a glitch, or am I missing something?

Using: Excel 2007 (12.0.6300.5000) SP1 MSO (12.0.6213.1000)

RESOLOVED
The problem I was having had nothing to do with cell references; it was a
logic error. Relative refs work fine in conditional formatting formulae.

What "method" did you use to set the CF?

Use the "Use a formula to determine which cells to format" method.

=C1=42


--
Biff
Microsoft Excel MVP


"spai461" wrote in message
...
I'm new to Excel 2007. Trying to apply conditional formatting based on a
formula to a whole column. In the 2003 version, if I used absolute
column/relative row references (e.g., "$c1=42"), the conditional
formatting
automatically adjusted the row references such that in the example above
it
would go $c1, $c2, $c3, etc...

But, in this version if I select a column and use a relative reference, it
only works in the first row; and if I check the rule for just one cell
with
in that column it shows the same reference as in the first row (i.e.,
$c1),
and it's the same in every row for the condition. Yet, the conditional
formatting help subject says explicitly in a note that relative references
are adjusted for the selected range.

Is this a glitch, or am I missing something?

Using: Excel 2007 (12.0.6300.5000) SP1 MSO (12.0.6213.1000)

I'm having the same issue, but have not found the resolution as you did.
What gives? I still can't get the cf to extend down a column with the
correct references - it still wants to compare to the first row. Thanks for
any help you can provide.

"spai461" wrote:

I'm new to Excel 2007. Trying to apply conditional formatting based on a
formula to a whole column. In the 2003 version, if I used absolute
column/relative row references (e.g., "$c1=42"), the conditional formatting
automatically adjusted the row references such that in the example above it
would go $c1, $c2, $c3, etc...

But, in this version if I select a column and use a relative reference, it
only works in the first row; and if I check the rule for just one cell with
in that column it shows the same reference as in the first row (i.e., $c1),
and it's the same in every row for the condition. Yet, the conditional
formatting help subject says explicitly in a note that relative references
are adjusted for the selected range.

Is this a glitch, or am I missing something?

Using: Excel 2007 (12.0.6300.5000) SP1 MSO (12.0.6213.1000)

Hi, I have the same problem. When you paste you cannot decide with $ if
change automatic or not the row number. Nothing to do with excel 2007. Some
one has resolved the problem? Thanks

"spai461" wrote:

I'm new to Excel 2007. Trying to apply conditional formatting based on a
formula to a whole column. In the 2003 version, if I used absolute
column/relative row references (e.g., "$c1=42"), the conditional formatting
automatically adjusted the row references such that in the example above it
would go $c1, $c2, $c3, etc...

But, in this version if I select a column and use a relative reference, it
only works in the first row; and if I check the rule for just one cell with
in that column it shows the same reference as in the first row (i.e., $c1),
and it's the same in every row for the condition. Yet, the conditional
formatting help subject says explicitly in a note that relative references
are adjusted for the selected range.

Is this a glitch, or am I missing something?

Using: Excel 2007 (12.0.6300.5000) SP1 MSO (12.0.6213.1000)

Are you sure you resolved? If I copy and paste conditional formatting in a
single cell it goes very well, but if I copy on a multiple cells I had the
problem that you described and the $ doesn't works (doesn' change the number
of row but use a range). Who can help me? Thanks

"spai461" wrote:

RESOLOVED
The problem I was having had nothing to do with cell references; it was a
logic error. Relative refs work fine in conditional formatting formulae.

The problem as I see it is not the conditional formatting, but Excel's
insistence on applying absolute references on the "Applies to" cells.

I selected a range of cells and applied a formula for conditional formatting
that works as expected on this selected range of cells. Yet when I try to
copy this rule to other rows, as previously noted, it just increases the
range of cells that gets affected by it. I want to apply this same rule as a
separate rule to a different row.

Is this possible? If so, please tell me how?

"reylon" wrote:

Are you sure you resolved? If I copy and paste conditional formatting in a
single cell it goes very well, but if I copy on a multiple cells I had the
problem that you described and the $ doesn't works (doesn' change the number
of row but use a range). Who can help me? Thanks

"spai461" wrote:

RESOLOVED
The problem I was having had nothing to do with cell references; it was a
logic error. Relative refs work fine in conditional formatting formulae.

"reylon" wrote:

Hi, I have the same problem. When you paste you cannot decide with $ if
change automatic or not the row number. Nothing to do with excel 2007. Some
one has resolved the problem? Thanks

"spai461" wrote:

I'm new to Excel 2007. Trying to apply conditional formatting based on a
formula to a whole column. In the 2003 version, if I used absolute
column/relative row references (e.g., "$c1=42"), the conditional formatting
automatically adjusted the row references such that in the example above it
would go $c1, $c2, $c3, etc...

But, in this version if I select a column and use a relative reference, it
only works in the first row; and if I check the rule for just one cell with
in that column it shows the same reference as in the first row (i.e., $c1),
and it's the same in every row for the condition. Yet, the conditional
formatting help subject says explicitly in a note that relative references
are adjusted for the selected range.

Is this a glitch, or am I missing something?

Using: Excel 2007 (12.0.6300.5000) SP1 MSO (12.0.6213.1000)

"reylon" wrote:

Hi, I have the same problem. When you paste you cannot decide with $ if
change automatic or not the row number. Nothing to do with excel 2007. Some
one has resolved the problem? Thanks

"spai461" wrote:

I'm new to Excel 2007. Trying to apply conditional formatting based on a
formula to a whole column. In the 2003 version, if I used absolute
column/relative row references (e.g., "$c1=42"), the conditional formatting
automatically adjusted the row references such that in the example above it
would go $c1, $c2, $c3, etc...

But, in this version if I select a column and use a relative reference, it
only works in the first row; and if I check the rule for just one cell with
in that column it shows the same reference as in the first row (i.e., $c1),
and it's the same in every row for the condition. Yet, the conditional
formatting help subject says explicitly in a note that relative references
are adjusted for the selected range.

Is this a glitch, or am I missing something?

Using: Excel 2007 (12.0.6300.5000) SP1 MSO (12.0.6213.1000)

I'm trying to apply a format as a table and it is not available why not and
how do I get it where I can apply it

"Patti F" wrote:

How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.

The answer can be found here
http://www.free-training-tutorial.co...ormatting.html

The key is when setting the conditional cell remove the the absolute row
reference. Example: I want A2:B12 to turn blue if C2:C12 is greater than 5.

I highlight A2:B12
Select Conditional FormattingManage Rules
Select new rule
Select Use formula to determine which cells to format
Place cursor in text field or click on cell selection box
Select first cell in condition column (C2)
Enter formula =$c$25
Then remove the $ in front of the 2 so that the formula reads: =$C25
Set the format to desired color (Blue)
Click Ok
Click Apply.

It worked for me in highlighting my grade book by letter grade. At a glance
i see the A's B's and C's.

Hope this helps.

"Nickelberry" wrote:

I'm trying to apply a format as a table and it is not available why not and
how do I get it where I can apply it

"Patti F" wrote:

How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.

I have an excel file with over 2000 lines. The data that was imported was
not consistent. One of the fields either had one or two data items, and as a
result, if the field had 2 data values, the 2nd data item was moved into the
A column of the row underneath it. These are all email address. Is there a
way I can create a rule and have them moved into the correct column (up one
row and into column C)?

This also meant the B column next to the email address now needs to be
deleted.

Can anyone tell me what I need to do? Thanks.

Any way to consistently tell which entry has two rows and which has one row?

Post a sample of some rows.


Gord Dibben MS Excel MVP

On Fri, 19 Dec 2008 05:34:01 -0800, Tom
wrote:

I have an excel file with over 2000 lines. The data that was imported was
not consistent. One of the fields either had one or two data items, and as a
result, if the field had 2 data values, the 2nd data item was moved into the
A column of the row underneath it. These are all email address. Is there a
way I can create a rule and have them moved into the correct column (up one
row and into column C)?

This also meant the B column next to the email address now needs to be
deleted.

Can anyone tell me what I need to do? Thanks.

Thank you for this. I understood the conditional/absolute references, or
thought I did, but I didn't realize that the relative reference could be
divided up; meaning, I was putting something like =C25, instead of =$C25.

For those who don't understand the difference, as I didn't, the dollar sign
in front of the 'C' means that the column 'C' will always be the reference
(absolutely defined), while the row (the '2' without the dollar sign) will
change with each row being evaluated (relatively defined).

The tutorials linked above are also pretty decent, though I didn't sit all
the way through any of them.

Again, thanks for this clarification and example.

"MacHerb" wrote:

The answer can be found here
http://www.free-training-tutorial.co...ormatting.html

The key is when setting the conditional cell remove the the absolute row
reference. Example: I want A2:B12 to turn blue if C2:C12 is greater than 5.

I highlight A2:B12
Select Conditional FormattingManage Rules
Select new rule
Select Use formula to determine which cells to format
Place cursor in text field or click on cell selection box
Select first cell in condition column (C2)
Enter formula =$c$25
Then remove the $ in front of the 2 so that the formula reads: =$C25
Set the format to desired color (Blue)
Click Ok
Click Apply.

It worked for me in highlighting my grade book by letter grade. At a glance
i see the A's B's and C's.

Hope this helps.

"Nickelberry" wrote:

I'm trying to apply a format as a table and it is not available why not and
how do I get it where I can apply it

"Patti F" wrote:

How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.

The problem is that the row reference does not get updated in each cell. I'm
checing for the length fo the trimmed value in C2-C300, that it not be
greater than 40 (=LEN(TRIM($C2))40. I did what the person 2 posts earlier
did, which is what I did in an earlier try. The row reference does not get
updated despite the absence of the $. HOWEVER, when I go to one of the
cells in the range( went tot he bottom) the formula works and any entry
longer than 40 is highlighted in red and Bold, as I wished. That damned
reference just doesn't get updated so at face value it looks like the formula
is dependent on what is in C2. This is a bug, I believe. I've been using
Excel, heavily, since the first version, and other SS before that. I hope
they fix it.

"Exirtis" wrote:

Thank you for this. I understood the conditional/absolute references, or
thought I did, but I didn't realize that the relative reference could be
divided up; meaning, I was putting something like =C25, instead of =$C25.

For those who don't understand the difference, as I didn't, the dollar sign
in front of the 'C' means that the column 'C' will always be the reference
(absolutely defined), while the row (the '2' without the dollar sign) will
change with each row being evaluated (relatively defined).

The tutorials linked above are also pretty decent, though I didn't sit all
the way through any of them.

Again, thanks for this clarification and example.

"MacHerb" wrote:

The answer can be found here
http://www.free-training-tutorial.co...ormatting.html

The key is when setting the conditional cell remove the the absolute row
reference. Example: I want A2:B12 to turn blue if C2:C12 is greater than 5.

I highlight A2:B12
Select Conditional FormattingManage Rules
Select new rule
Select Use formula to determine which cells to format
Place cursor in text field or click on cell selection box
Select first cell in condition column (C2)
Enter formula =$c$25
Then remove the $ in front of the 2 so that the formula reads: =$C25
Set the format to desired color (Blue)
Click Ok
Click Apply.

It worked for me in highlighting my grade book by letter grade. At a glance
i see the A's B's and C's.

Hope this helps.

"Nickelberry" wrote:

I'm trying to apply a format as a table and it is not available why not and
how do I get it where I can apply it

"Patti F" wrote:

How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.

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.

"Tom" wrote:

I have an excel file with over 2000 lines. The data that was imported was
not consistent. One of the fields either had one or two data items, and as a
result, if the field had 2 data values, the 2nd data item was moved into the
A column of the row underneath it. These are all email address. Is there a
way I can create a rule and have them moved into the correct column (up one
row and into column C)?

This also meant the B column next to the email address now needs to be
deleted.

Can anyone tell me what I need to do? Thanks.

Thank you it helped a lot, I was already giving up on finding a solution :)

"Exirtis" wrote:

Thank you for this. I understood the conditional/absolute references, or
thought I did, but I didn't realize that the relative reference could be
divided up; meaning, I was putting something like =C25, instead of =$C25.

For those who don't understand the difference, as I didn't, the dollar sign
in front of the 'C' means that the column 'C' will always be the reference
(absolutely defined), while the row (the '2' without the dollar sign) will
change with each row being evaluated (relatively defined).

The tutorials linked above are also pretty decent, though I didn't sit all
the way through any of them.

Again, thanks for this clarification and example.

"MacHerb" wrote:

The answer can be found here
http://www.free-training-tutorial.co...ormatting.html

The key is when setting the conditional cell remove the the absolute row
reference. Example: I want A2:B12 to turn blue if C2:C12 is greater than 5.

I highlight A2:B12
Select Conditional FormattingManage Rules
Select new rule
Select Use formula to determine which cells to format
Place cursor in text field or click on cell selection box
Select first cell in condition column (C2)
Enter formula =$c$25
Then remove the $ in front of the 2 so that the formula reads: =$C25
Set the format to desired color (Blue)
Click Ok
Click Apply.

It worked for me in highlighting my grade book by letter grade. At a glance
i see the A's B's and C's.

Hope this helps.

"Nickelberry" wrote:

I'm trying to apply a format as a table and it is not available why not and
how do I get it where I can apply it

"Patti F" wrote:

How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.

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


"Chetski" wrote:

The problem as I see it is not the conditional formatting, but Excel's
insistence on applying absolute references on the "Applies to" cells.

I selected a range of cells and applied a formula for conditional formatting
that works as expected on this selected range of cells. Yet when I try to
copy this rule to other rows, as previously noted, it just increases the
range of cells that gets affected by it. I want to apply this same rule as a
separate rule to a different row.

Is this possible? If so, please tell me how?

"reylon" wrote:

Are you sure you resolved? If I copy and paste conditional formatting in a
single cell it goes very well, but if I copy on a multiple cells I had the
problem that you described and the $ doesn't works (doesn' change the number
of row but use a range). Who can help me? Thanks

"spai461" wrote:

RESOLOVED
The problem I was having had nothing to do with cell references; it was a
logic error. Relative refs work fine in conditional formatting formulae.