get the active color of a cell
I got cells that had there color chaging automatically using a format
condition (cond#1:red if B4<10 cond#2:green if C5>100 for example...).
Now, I would like to automatically change the color to blue if the
active color is red (condition #1 true in my example).
I can get the ColorIndex of the cell:
-when no condition applied = .Font.ColorIndex
-when condition #1 applied = .FormatConditions(1).Font.ColorIndex
-when condition #2 applied = .FormatConditions(2).Font.ColorIndex
But impossible to retrieve the actual cell color... any idea?
Another way to solve my problem would be to be able ...cell does not auto-stretch when too much text in one cell
I have text of multiple lines in a cell. It seems after it reaching to
an amount, it stops auto-stretching so I can see all text in the cell.
I have manually drag it down so I can see the complete content. Is
there a way to have it auto-stretched to show all text again?
Thanks for the help,
Use Format/Row/Autofit -- you'll only have to do it once. From then on it
should "auto stretch".
"cpliu" <email@example.com> wrote in message
>I have text of multiple lines in a cel...format to see bottom of cell
I want to find out if there is a way to format a cell so I can view the
bottom half of the cell because there are more text then the height of the
cell. I don't want to increase the height of the cell.
Thanks in advance
Maybe an indirect way would be to write the text in the cells into their own
comment boxes ? Then, hovering the mouse over the cell will display the
desired full text in the cell w/o having to increase the row height.
You might wish to check out Dave M's page at:
Look for: Sub AddComments()
The sub will popula...Increment a number based on four adjoing cells
I am using an xlsx spreadsheet for indexing metadata for file folders. Based
on the metadata selected from dropdowns in columns H, I & J, the spreadsheet
builds a file number in the format AA-AA-NN-nnnnn (A=alpha; N=predetermined
number; nnnnn= sequential number).
I'm using the following formulas:
Col B =VLOOKUP(H577,FUNCTIONLU,2)
Col C =VLOOKUP(I577,CATEGORYLU,2)
Col D =VLOOKUP(J577,TYPELU,2)
=IF(D576="","",IF(COUNTIF($D$2:$D576,$D576)=1,TEXT(0,"00001")+0,IF(COUNTIF($D$2:$D576,D576)>1,INDEX($E$2:$E576,MATCH($D576,$D$2:$...VLOOKUP formula displays in cell, will not return data
I had an older version of Excel (97 maybe?) and our office updated to 2007.
Now my vlookup will not return data. The vlookup formula appears in the
cell or I get the NA#. I've formatted my lookup cells and table to General
but still get the same result.
After formatting the cell as General, re-enter it (F2/Enter) - if you're
still seeing the formula, press Ctrl/~ -- you're looking at the formula layer
instead of the value layer.
> I had an older version of Excel (97 maybe?) and our office updated to 2007.
> Now my vloo...I want to format a number cell without the decimal and without ro.
I need to format the number cell so that it will still show the numbers after
the decimal, but not show the decimal point at all. It must also display
leading zeros, which is not a problem. I just need a way to strip the
decimal from the number.
If if needs to stay a number with the same value, I don't think you can do it.
If you can change the value Just multiple the number by ten to the number of
If it can be text set up a new column and use
> I need to format the number cell so that it will sti...CRM 3.0 Tracking Token formatting
Version 3.0 offers significant improvement in the tracking token (no
more 32 char guids!). However, is there a way to move the tracking
token a bit more to the right of the subject line. The tracking token
is appended right next to the subject message and is confusing ..
especially to the recipient. Version 1.2 right justified (to a certain
extent) the token on CRM promoted emails.
Thanks in advance for your assistance,
Tax Credit Company
At this time there is not control to that extent over the tracking token
Microsoft CRM M...How can setup the receipt format automatical reprint for a seriali
How can edit the (.xml) receipt format to automatic reprint for a serialize
item in the transation? This would be great for rebates with serialized items.
...Can Excel autosize merged cells yet?
Begging everyone's indulgence with an old Q:
I'm using a somewhat dated version of Excel. (Where it says "Tools" on
the menu, it means "polished stones".)
In the past, Excel couldn't autosize merged cells--thereby preventing,
oh, that small segment of Earth's population that is actually literate
from writing prose in Excel, without having to eyeball and manually
adjust the height of every miserable row thruout your entire workbook
containing prose wrapped across merged cells.
A quick search of microsoft.public.excel.* shows that many ancestors
of Homo fen...Formating text in colums to copy to other applications
any help would be greatly received.
What I am trying to achieve is to format text in order for me to cu
and paste into other applications.
The text contains the item description in one column and the price i
another column, however when I highlight and copy then paste into th
application in work the columns are not formatted correctly and th
price column is all over the place.
e..g how I want it to look
price of item code 1234 (spanner) �63.00
price of item code 23 (hammer) �29.00
price of item code 9 (o/s) �26.13
e.g how it actually looks
price of item code 1234 (spanner) �63.00...date in wrong format
My mo/da/yr format somehow changes to a 5 digit entry - 393xx. I have tied
the normal procedure of format cells, date, etc. but that won't fix it. What
can I do?
Mike this is a little long winded but I found it helpful in resolving many a
> My mo/da/yr format somehow changes to a 5 digit entry - 393xx. I have tied
> the normal procedure of format cells, date, etc. but that won't fix it. What
> can I do?
You should also be able to type 1 into a cell, copy it an...2 Conditional formats and insert comment
1. I want to format the cell by 2 conditions. e.g. in range a1:z1, fill the
red colour(Fill colour) only if it is blank and if it contains "Sunday",
show it with red colour (Font Colour). HOw can I do this?
2. I want to insert the comment conditionally e.g. if the cell contains
"IT" insert the comment "India Today", "TOI" insert comment as "Times of
India" etc. How Can I
perform this operation?
Knowldege is Power
> 1. I want to format the cell by 2 conditions. e.g. in range a1:z1, fill
> cell with
> re...Vlookup from different sheets
I have the below data...like this i have huge data in different sheets(which
are named date wise) and i can not consolidate them in a single sheet as the
lines are more than 65000.The account numbers are repeting many a times in
In a consolidated sheets i have the unique account numbers where i want the
name of the account holder... for this i need to use vlookup formula from
alomost 20-22 sheets.is there any formula whereby i can use the vlookup from
4541112 ...Cell Formatting with a macro...
Related to a previous topic.
I have a worksheet that uses conditional formatting. However, I need
to add a 4th condition. I need to code a macro that checks the
content of a cell, and then changes the formating (color) of a range
of cells if the condition is met.
For example if cell M1 is populated with an 'X', then I want excel to
change the color of the cells (pattern) for the range A1:M1.
Basically, the macro will check for values in the M column and
highlight all the cells in the intersecting row.
I used conditional formating to perform other tasks, but since Excel
only supp...Different formatting in a cell with a concatenated formula? #2
Yes, if a1 = blue, and b1 = red, and the formula in cell c1 is =a1&b1,
want the result to look like this: bluere
andy2812's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1596
View this thread: http://www.excelforum.com/showthread.php?threadid=27440
Formulas don't support this character by character formatting. If you can
convert the results of the formula to values, you can do it though--but that
destroys the formula.
> Yes, if a1 = blue, and...Writing an expression using fields with different formats
I need to subtract a field sum from another, but they are in different
formats. How can I convert one of themor get one to recognize the other?
Total Shift Time (hh:nn:ss)
Wrap Up Time (standard)
Need to do the following expression:
[Total Shift Time]-[Wrap Up Time]
The problem is I'm pulling the data from two different sources, one provdies
it in hh:nn:ss format, and the other provides the data in standard number
Format does not matter. What matters is the datatype of the field it is
stored in. Are they the same or compatible?
A datetime fields can be subtrac...isna vlookup
I'm using this vlookup to get values from a sheet
named "sub": VLOOKUP(R1,sub!$J$1:$K$18,2,FALSE)
and t works. But when "R1" is not in the lookup table
sub!$J$1:$K$18 I get an #N/A as a result and I changed
the vlookup formula to:
(R1,sub!$J$1:$K$18,2,FALSE)) to avoid #N/A but now I
receive an error message for the formula.
Is there anybody who knows how to fix this problem?
> I'm using this vlookup to get values from a sheet
> named "sub": VLOOKU...I'm I the only one pissed off at BofA for changing the format they use for reporting transactions?
Instead of putting the location where the transaction took place, the
transaction entry now starts with: BANK CARD PURCHASE
WTF is that? Now I'm forced to manually change the payee so that the
Am I the only one noticing this? If not, please send an email to them
about it through their site. Maybe if enough people complain they'll
fix this problem. Its a long shot, but worth a try.
njohn said the following:
> Instead of putting the location where the transaction took place, the
> transaction entry now starts with: BANK CARD PURCHA...cell outside range
Intermittently, a cell erroneously appears outside the range of cells in an
active worksheet. This cell contains duplicate data from one of the other
cells. I am not able to replicate the sequence of events that causes this
cell outside the range of cells to magically appear. I wonder if anyone else
has seen this problem.
Are there any macros in any open workbook?
"Mrs. T." wrote:
> Intermittently, a cell erroneously appears outside the range of cells in an
> active worksheet. This cell contains duplicate data from one of the other
> cells. I am ...format cells #4
hi. how can i set by default the cell formatting to 'general' or
'number' or even 'text'?
i mean i dont want excel to correct let's say: 1.5 to 01.may or 9/12 to
09.dec. is this possible?
(i have some programs which export data to excel and instead of numbers
in excel appear dates and i can't set the cell format before the data is
notrace2004's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34817
View this thread: http://www....how do i write a sentence in a cell?
im reli confused i dont no how to write a sentence in a exel cell without it
running into the cell next to it n its buggin me cos im having to ut a good
paragragh in a cell n its not workin
Select the column, say col A
Click Format > Cells > Alignment tab
Check "Wrap text" > OK
Now when you type in col A, the text will wrap around w/o running into the
next cell. You could of course, increase the width of the column by dragging
the right edge of the column header to the right, etc
And if you need to break for a new line within the same cell,
press Alt + ENTER
Ma...Text don't fit in a cell
Is there a limit to the text count in a cell? I have wrap
text on and I even increased my height and width but I can
only see up to a certain point of my entry. I even tried
making my font smaller but all it does is give me the
extra space but still end at the same spot of my entry.
If there is a limit, is there a way to override that?
there's a limit of 1,024 characters per cell (in the formula you can
see up to 32K). You can extened this limit by manually entering
linebreaks with ALT+ENTER
"Sue K" <anonym...How to have TAB name to appear in a Excel cell?
How do I get the Worksheet name to appear in an Excel cell?
"TAB name to appear in a Excel cell?" <TAB name to appear in a Excel
firstname.lastname@example.org> wrote in message
> How do I get the Worksheet name to appear in an Excel cell?
"TAB name to appear in a Excel cell?" <TAB name to appear in a Excel
I use excel as a database for storing / manipulating info
for my web site which deals with bird checklists. The bird
names are entered as follows... Common Buzzard (bold)
space, Buteo buteo (italicised). My problem is that I have
to merge the two tables (bold name) & (latin name in
italics) into one cell but I cannot get the formating to
stay. That is, the italicised name always follows the
original cell format. Is there a symbol or control symbol
I can insert between the bold and italic names to maintain
the formating? I can change the format manually but with
20,000 entries to c...lookup value & formatting
Is it possible to undertake a vlookup function or such
like which not only imports the value that is found, but
also the format of the value, be it a different font or
Only by using VBA. I suggest the following :-
1. Select cell with value
2. Find the value in the lookup sheet
3. Copy the cell containing the return value.
3. Go back to the original sheet & Paste (with format)
Something like this (you will need to amend as necessary) :-