P
Patti F
How can I format an entire column using conditional format? I keep getting an
error message when I choose the column.
error message when I choose the column.
P
Peo Sjoblom
Yes but you do it by first selecting the column, then applying the format on
the active cell
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
the active cell
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
E
Elkar
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.
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.
S
spai461
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)
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)
S
spai461
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 I was having had nothing to do with cell references; it was a
logic error. Relative refs work fine in conditional formatting formulae.
T
T. Valko
What "method" did you use to set the CF?
Use the "Use a formula to determine which cells to format" method.
=C1=42
Use the "Use a formula to determine which cells to format" method.
=C1=42
T
tarday
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.
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.
R
reylon
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
change automatic or not the row number. Nothing to do with excel 2007. Some
one has resolved the problem? Thanks
R
reylon
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
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
C
Chetski
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?
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?
J
JCas
reylon said:
J
JCas
reylon said:
N
Nickelberry
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
how do I get it where I can apply it
M
MacHerb
The answer can be found here
http://www.free-training-tutorial.com/conditional-formatting.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 Formatting>Manage 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$2>5
Then remove the $ in front of the 2 so that the formula reads: =$C2>5
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.
http://www.free-training-tutorial.com/conditional-formatting.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 Formatting>Manage 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$2>5
Then remove the $ in front of the 2 so that the formula reads: =$C2>5
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.
T
Tom
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.
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.
G
Gord Dibben
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
Post a sample of some rows.
Gord Dibben MS Excel MVP
E
Exirtis
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 =C2>5, instead of =$C2>5.
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.
thought I did, but I didn't realize that the relative reference could be
divided up; meaning, I was putting something like =C2>5, instead of =$C2>5.
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.
G
georgelf
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.
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.
G
georgelf
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.
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.
E
Eve
Thank you it helped a lot, I was already giving up on finding a solution 