Two Conditional Formats

The first conditional format is "=MOD(ROW(),2)=0". This auto shades every 
other row and fixs the rows color so if I do a sort the rows will still be 
shaded every other row.

The second Conditional Format is to color the font red if less than 0.  
However, if a negitive number is in a row that has been shaded, the font will 
not trun red, in the other rows the negatives are red.

What can I do to have both the shaded cell and red negative numbers?

As always, any help is very much appreciated.
0
Ronbo (18)
7/11/2005 6:48:08 PM
excel.misc 78881 articles. 5 followers. Follow

10 Replies
279 Views

Similar Articles

[PageSpeed] 5


"Ronbo" wrote:

> The first conditional format is "=MOD(ROW(),2)=0". This auto shades every 
> other row and fixs the rows color so if I do a sort the rows will still be 
> shaded every other row.
> 
> The second Conditional Format is to color the font red if less than 0.  
> However, if a negitive number is in a row that has been shaded, the font will 
> not trun red, in the other rows the negatives are red.
> 
> What can I do to have both the shaded cell and red negative numbers?
> 
> As always, any help is very much appreciated.

I just noticed that it works correctly for (Dollars)  but not (Percentage)  
???
0
Ronbo (18)
7/11/2005 6:58:07 PM
Make the first conditional format be both the mod shade and the less than 
zero red font. then the other two conditionals be the mod and the less than 
zero

"Ronbo" wrote:

> The first conditional format is "=MOD(ROW(),2)=0". This auto shades every 
> other row and fixs the rows color so if I do a sort the rows will still be 
> shaded every other row.
> 
> The second Conditional Format is to color the font red if less than 0.  
> However, if a negitive number is in a row that has been shaded, the font will 
> not trun red, in the other rows the negatives are red.
> 
> What can I do to have both the shaded cell and red negative numbers?
> 
> As always, any help is very much appreciated.
0
BJ (832)
7/11/2005 7:01:02 PM
Ronbo,

You need 3 CF's.  The first should be

=AND(MOD(ROW(),2)=0,A1<0)

formatted red font, shaded background.

Then your other two as before.

HTH,
Bernie
MS Excel MVP


"Ronbo" <Ronbo@discussions.microsoft.com> wrote in message 
news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
> The first conditional format is "=MOD(ROW(),2)=0". This auto shades every
> other row and fixs the rows color so if I do a sort the rows will still be
> shaded every other row.
>
> The second Conditional Format is to color the font red if less than 0.
> However, if a negitive number is in a row that has been shaded, the font will
> not trun red, in the other rows the negatives are red.
>
> What can I do to have both the shaded cell and red negative numbers?
>
> As always, any help is very much appreciated. 


0
Bernie
7/11/2005 7:05:39 PM
Thanks to both for the help.  However, I am not getting it to work. It takes 
all shading out and changes the font on rows without shading.  Same as before.
I did it twice and checked each time for a grammer errors.

I am conditionally formating columns.

Any ideas what I might be doing wrong?




"Bernie Deitrick" wrote:

> Ronbo,
> 
> You need 3 CF's.  The first should be
> 
> =AND(MOD(ROW(),2)=0,A1<0)
> 
> formatted red font, shaded background.
> 
> Then your other two as before.
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> "Ronbo" <Ronbo@discussions.microsoft.com> wrote in message 
> news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
> > The first conditional format is "=MOD(ROW(),2)=0". This auto shades every
> > other row and fixs the rows color so if I do a sort the rows will still be
> > shaded every other row.
> >
> > The second Conditional Format is to color the font red if less than 0.
> > However, if a negitive number is in a row that has been shaded, the font will
> > not trun red, in the other rows the negatives are red.
> >
> > What can I do to have both the shaded cell and red negative numbers?
> >
> > As always, any help is very much appreciated. 
> 
> 
> 
0
Ronbo (18)
7/11/2005 7:38:07 PM
when you are in, say C3, what does the conditional formating say for each 
condition?
additionally, what do you mean when you say you are formatting columns?

"Ronbo" wrote:

> Thanks to both for the help.  However, I am not getting it to work. It takes 
> all shading out and changes the font on rows without shading.  Same as before.
> I did it twice and checked each time for a grammer errors.
> 
> I am conditionally formating columns.
> 
> Any ideas what I might be doing wrong?
> 
> 
> 
> 
> "Bernie Deitrick" wrote:
> 
> > Ronbo,
> > 
> > You need 3 CF's.  The first should be
> > 
> > =AND(MOD(ROW(),2)=0,A1<0)
> > 
> > formatted red font, shaded background.
> > 
> > Then your other two as before.
> > 
> > HTH,
> > Bernie
> > MS Excel MVP
> > 
> > 
> > "Ronbo" <Ronbo@discussions.microsoft.com> wrote in message 
> > news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
> > > The first conditional format is "=MOD(ROW(),2)=0". This auto shades every
> > > other row and fixs the rows color so if I do a sort the rows will still be
> > > shaded every other row.
> > >
> > > The second Conditional Format is to color the font red if less than 0.
> > > However, if a negitive number is in a row that has been shaded, the font will
> > > not trun red, in the other rows the negatives are red.
> > >
> > > What can I do to have both the shaded cell and red negative numbers?
> > >
> > > As always, any help is very much appreciated. 
> > 
> > 
> > 
0
BJ (832)
7/11/2005 8:16:07 PM
Lets use h3, h4

h3=

Condition 1
     =MOD(ROW(),2)=0,H3<0
Condition 2
     =MOD(ROW(),2)=0
Condition 3
     CellValue - less than - 0 - Format = Red Fonts

I am Conditional Formating a column at a time i.e. Highlight column H and 
put in CF.  Each of the references in H1 change to the appropriate cell so in 
cell h4 it would be 
=MOD(ROW(),2)=0,H4<0






"bj" wrote:

> when you are in, say C3, what does the conditional formating say for each 
> condition?
> additionally, what do you mean when you say you are formatting columns?
> 
> "Ronbo" wrote:
> 
> > Thanks to both for the help.  However, I am not getting it to work. It takes 
> > all shading out and changes the font on rows without shading.  Same as before.
> > I did it twice and checked each time for a grammer errors.
> > 
> > I am conditionally formating columns.
> > 
> > Any ideas what I might be doing wrong?
> > 
> > 
> > 
> > 
> > "Bernie Deitrick" wrote:
> > 
> > > Ronbo,
> > > 
> > > You need 3 CF's.  The first should be
> > > 
> > > =AND(MOD(ROW(),2)=0,A1<0)
> > > 
> > > formatted red font, shaded background.
> > > 
> > > Then your other two as before.
> > > 
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > > 
> > > 
> > > "Ronbo" <Ronbo@discussions.microsoft.com> wrote in message 
> > > news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
> > > > The first conditional format is "=MOD(ROW(),2)=0". This auto shades every
> > > > other row and fixs the rows color so if I do a sort the rows will still be
> > > > shaded every other row.
> > > >
> > > > The second Conditional Format is to color the font red if less than 0.
> > > > However, if a negitive number is in a row that has been shaded, the font will
> > > > not trun red, in the other rows the negatives are red.
> > > >
> > > > What can I do to have both the shaded cell and red negative numbers?
> > > >
> > > > As always, any help is very much appreciated. 
> > > 
> > > 
> > > 
0
Ronbo (18)
7/11/2005 9:12:02 PM
Yes, you need three CFs.

CF1:   =AND(A1<0,MOD(ROW(),2)=0)	(shade cell and red font)
CF2:   =AND(A1>=0,MOD(ROW(),2)=0)       (shade cell and  black font)
CF3:   =AND(A1<0,MOD(ROW(),2)=1)         (no shade and red font)

Regards,
B.R.Ramachandran		

"Ronbo" wrote:

> The first conditional format is "=MOD(ROW(),2)=0". This auto shades every 
> other row and fixs the rows color so if I do a sort the rows will still be 
> shaded every other row.
> 
> The second Conditional Format is to color the font red if less than 0.  
> However, if a negitive number is in a row that has been shaded, the font will 
> not trun red, in the other rows the negatives are red.
> 
> What can I do to have both the shaded cell and red negative numbers?
> 
> As always, any help is very much appreciated.
0
7/11/2005 9:48:02 PM
You left out the AND:

  =AND(MOD(ROW(),2)=0,H3<0)

HTH,
Bernie
MS Excel MVP

"Ronbo" <Ronbo@discussions.microsoft.com> wrote in message 
news:9955F6C9-BF24-48C8-B8BC-A411DDC32CEB@microsoft.com...
> Lets use h3, h4
>
> h3=
>
> Condition 1
>     =MOD(ROW(),2)=0,H3<0
> Condition 2
>     =MOD(ROW(),2)=0
> Condition 3
>     CellValue - less than - 0 - Format = Red Fonts
>
> I am Conditional Formating a column at a time i.e. Highlight column H and
> put in CF.  Each of the references in H1 change to the appropriate cell so 
> in
> cell h4 it would be
> =MOD(ROW(),2)=0,H4<0
>
>
>
>
>
>
> "bj" wrote:
>
>> when you are in, say C3, what does the conditional formating say for each
>> condition?
>> additionally, what do you mean when you say you are formatting columns?
>>
>> "Ronbo" wrote:
>>
>> > Thanks to both for the help.  However, I am not getting it to work. It 
>> > takes
>> > all shading out and changes the font on rows without shading.  Same as 
>> > before.
>> > I did it twice and checked each time for a grammer errors.
>> >
>> > I am conditionally formating columns.
>> >
>> > Any ideas what I might be doing wrong?
>> >
>> >
>> >
>> >
>> > "Bernie Deitrick" wrote:
>> >
>> > > Ronbo,
>> > >
>> > > You need 3 CF's.  The first should be
>> > >
>> > > =AND(MOD(ROW(),2)=0,A1<0)
>> > >
>> > > formatted red font, shaded background.
>> > >
>> > > Then your other two as before.
>> > >
>> > > HTH,
>> > > Bernie
>> > > MS Excel MVP
>> > >
>> > >
>> > > "Ronbo" <Ronbo@discussions.microsoft.com> wrote in message
>> > > news:EEB9EB84-F662-4D03-97C5-4E6A839617EF@microsoft.com...
>> > > > The first conditional format is "=MOD(ROW(),2)=0". This auto shades 
>> > > > every
>> > > > other row and fixs the rows color so if I do a sort the rows will 
>> > > > still be
>> > > > shaded every other row.
>> > > >
>> > > > The second Conditional Format is to color the font red if less than 
>> > > > 0.
>> > > > However, if a negitive number is in a row that has been shaded, the 
>> > > > font will
>> > > > not trun red, in the other rows the negatives are red.
>> > > >
>> > > > What can I do to have both the shaded cell and red negative 
>> > > > numbers?
>> > > >
>> > > > As always, any help is very much appreciated.
>> > >
>> > >
>> > > 


0
Bernie
7/12/2005 12:37:55 AM
B. R.Ramachandran

Thanks for your help, however it did not work.  It stripped the "Alternative 
Row Shading" (*1) away.

What I did was highlighted rows 20:100 then did a conditional format of;
     =MOD(ROW(),2)=0       (*1)        

This works perfect.  It alternatively shades rows the color you want and it 
fixes the color to the row so that when sorting/adding/deleting it will still 
have alternatively shaded rows.

I want to shade all negitive numbers red. For dollars I "Format Cells" with 
Dollars - (red) and it works perfect.  But with percentage that option is not 
available.  So I have been trying conditional formating and the above 
suggestions, but nothing works for percentage.

Again any help is appreciated.

(*1)  The code is from John Walenback at j-walk.com.  Sorry I did not 
recognize this before, but I did not have the programmers name.









"B. R.Ramachandran" wrote:

> Yes, you need three CFs.
> 
> CF1:   =AND(A1<0,MOD(ROW(),2)=0)	(shade cell and red font)
> CF2:   =AND(A1>=0,MOD(ROW(),2)=0)       (shade cell and  black font)
> CF3:   =AND(A1<0,MOD(ROW(),2)=1)         (no shade and red font)
> 
> Regards,
> B.R.Ramachandran		
> 
> "Ronbo" wrote:
> 
> > The first conditional format is "=MOD(ROW(),2)=0". This auto shades every 
> > other row and fixs the rows color so if I do a sort the rows will still be 
> > shaded every other row.
> > 
> > The second Conditional Format is to color the font red if less than 0.  
> > However, if a negitive number is in a row that has been shaded, the font will 
> > not trun red, in the other rows the negatives are red.
> > 
> > What can I do to have both the shaded cell and red negative numbers?
> > 
> > As always, any help is very much appreciated.
0
Ronbo (18)
7/12/2005 1:45:03 AM
Hi Ronbo,

     I actually tested the CF formulas in a trial Excel spreadsheet before 
posting my suggestion to you.  The CF works (shades alternate rows, and also 
shows negative values in red font regardless of whether they are in shaded 
rows or not; furthermore, it works regardless of whether the cell contents 
are formatted as number, currency, or percentage).  
     I am giving the formulas again.  In  the Conditional Formatting window, 
select the 'Formula Is' (and not the 'Cell Value Is') option.  For condition 
1, the rows are shaded and the font is colored red (or whatever color you 
want); for condition 2, the rows are shaded but the font color is the default 
color (black); and for condition 3, there is no shading for the rows but the 
font is colored red.

Conditn 1:   Formula Is       =AND(A1<0,MOD(ROW(),2)=0)		
Conditn 2:   Formula Is       =AND(A1>=0,MOD(ROW(),2)=0)		
Conditn 3:   Formula Is       =AND(A1<0,MOD(ROW(),2)=1)

Regards,
B.R. Ramachandran		

"Ronbo" wrote:

> B. R.Ramachandran
> 
> Thanks for your help, however it did not work.  It stripped the "Alternative 
> Row Shading" (*1) away.
> 
> What I did was highlighted rows 20:100 then did a conditional format of;
>      =MOD(ROW(),2)=0       (*1)        
> 
> This works perfect.  It alternatively shades rows the color you want and it 
> fixes the color to the row so that when sorting/adding/deleting it will still 
> have alternatively shaded rows.
> 
> I want to shade all negitive numbers red. For dollars I "Format Cells" with 
> Dollars - (red) and it works perfect.  But with percentage that option is not 
> available.  So I have been trying conditional formating and the above 
> suggestions, but nothing works for percentage.
> 
> Again any help is appreciated.
> 
> (*1)  The code is from John Walenback at j-walk.com.  Sorry I did not 
> recognize this before, but I did not have the programmers name.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> "B. R.Ramachandran" wrote:
> 
> > Yes, you need three CFs.
> > 
> > CF1:   =AND(A1<0,MOD(ROW(),2)=0)	(shade cell and red font)
> > CF2:   =AND(A1>=0,MOD(ROW(),2)=0)       (shade cell and  black font)
> > CF3:   =AND(A1<0,MOD(ROW(),2)=1)         (no shade and red font)
> > 
> > Regards,
> > B.R.Ramachandran		
> > 
> > "Ronbo" wrote:
> > 
> > > The first conditional format is "=MOD(ROW(),2)=0". This auto shades every 
> > > other row and fixs the rows color so if I do a sort the rows will still be 
> > > shaded every other row.
> > > 
> > > The second Conditional Format is to color the font red if less than 0.  
> > > However, if a negitive number is in a row that has been shaded, the font will 
> > > not trun red, in the other rows the negatives are red.
> > > 
> > > What can I do to have both the shaded cell and red negative numbers?
> > > 
> > > As always, any help is very much appreciated.
0
7/12/2005 4:15:02 PM
Reply:

Similar Artilces:

Error in Formatting
Hello Experts, I have Excel 97 Pro on Win98SE. I had almost forgot it but has happened about three minutes ago. Sometimes, any kind of cell formatting causes following error: ========================================= EXCEL caused an invalid page fault in module MSO97.DLL at 017f:307333ca. Registers: EAX=02c4000c CS=017f EIP=307333ca EFLGS=00010207 EBX=00002080 SS=0187 ESP=0062d0f8 EBP=0062d428 ECX=00005dd5 DS=0187 ESI=00630000 FS=59f7 EDX=0001a606 ES=0187 EDI=02c42ebc GS=0000 Bytes at CS:EIP: f3 a5 8b ca 83 e1 03 f3 a4 5f 5e c3 8d 14 0e 3b Stack dump: 02c4000c 0062d150 30001065 02c4000c 006...

Date format??
Hi! I have an Excel file that I have imported into Access. My dates are broken up into four different fields and I need to combine those fields. I need my dates to be in the YYYYMMDD format. My only problem is that my Year, Month, and Day fields show only one digit when there is a 1-9 used. Example Century Year Month Day 20 1 5 16 19 98 12 8 19 84 9 20 20 3 10 15 In Excel, I tried to change the format of the number using a custom format, and it changed the look of the number, just not that value in the...

Email address format
Email addresses in our Exchange system are currently in the format of: FirstNameLastName@mydomain. We now want to separate the first and last name with a full stop. Is there somewhere I can change the format to: FirstName.LastName@mydomain and make this the default for any new users that are created? Many thanks Homer On Thu, 11 Nov 2004 01:49:06 -0800, "Homer" <Homer@discussions.microsoft.com> wrote: >Email addresses in our Exchange system are currently in the format of: >FirstNameLastName@mydomain. > >We now want to separate the first and last name with a...

count formatted cells
I would like to count the number of cells in a column that have been formatted a certain colour. is this possible? See http://cpearson.com/excel/colors.htm In article <cassec$p6b$3@news6.svr.pol.co.uk>, "bobtracey" <bob@traceyr.freeserve.co.uk> wrote: > I would like to count the number of cells in a column that have been > formatted a certain colour. is this possible? Only with macro. You should find code with a search, this is a ver common question -- Message posted from http://www.ExcelForum.com ...

formatting cells #6
Is there a way to set the width and heighth of a cell in inches? Thanks, Debbie Row heights are measured in points or pixels. There are 72 points to an inch and "maybe" 96 pixels to the inch. The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. For an interesting and enlightening discussion on this subject see http://snipurl.com/dzz8 If you want to use VBA to set height and width in mm which you can convert to inches. Ole Erlandson has code for setting row and column dimensions. http://...

more than 3 conditional formating in excel
Hi I am new to conditional formating in Excel. In row 2 I need to enter nos. between 1-5. I want each color to have a particular color. I have managed to do 4 (3 with conditional formating and the 4th retaining the default color). Is there a way i can do all 5 colors? Thanks You need to use VBA. Try this:- Private Sub Worksheet_Calculate() 'Code must be placed in the codemodule of the actual sheet you are working with. Dim oCell As Range For Each oCell In Range("A1:A20") Select Case oCell.Value Case Is < 1 oCell.Interior.Colo...

Outlook emails to pdf format?
How do I change a group of Outlook emails into pdj format? mahalo. karen kfletchb <kfletchb@discussions.microsoft.com> wrote: > How do I change a group of Outlook emails into pdj format? With a PDF writer like Adobe Acrobat or the free EasyPrint from Staples (http://www.staples.com/sbd/cre/programs/easyprint/index.html?storeId=10001&cm_ven=Glamour&cm_ite=easyprint) -- Brian Tillman Brian Tillman wrote: > kfletchb <kfletchb@discussions.microsoft.com> wrote: > >> How do I change a group of Outlook emails into pdj format? > > With a PDF writer ...

Lookup Problem Matching on Two Criteria
I have a lookup/match problem that I cannot seem to get a solution to. I am assuming I can use a combination IF statement with a nested VLOOKUP but it's not coming to me. I have a table with three columns. Column A has values S01 to S08. Column B has unique values but are associated with the values in column A. Column C has the target values I am trying to populate in a cell. So the problem is that I need to match a Column A value with a column B value to find the answer in column C. Please note that the values in Column A are duplicated multiple times depending on their relationship to ...

How do you merge two spreadsheets to update data.
I have two spreadsheets with all columns and headings the same. One heading is vendor numbers. The master spreadsheet I get contains many more vendors that I don't want to track. I want to update certain vendors by vendor number from one master spreadsheet every month and update the same info with a selected list of vendors every month. How do i do it. Hi, I would assume that some form of lookup will suffice, such as an INDEX or VLOOKUP. Use your help menu for a detailed description and examples on how they work. Once you get your head around them they're quite easy. Cheers,...

Formating # of decimal places displayed in Textbox
using access 2003 and Win xp pro I have a problem with formatting a form textbox to display the the correct number of decimals. The text box is a calculated field that is [grams_unit] * [quanity]. Grams_unit is in a table as field size: "single", format: "general number", decimal places: "2". In the table the grams_unit displays as .85. However when I do the calculation for my textbox (totalgramsTB) 1*grams_unit yields 0.850000023841858 and it displays 15 decimal places. What I would like is for it to display only .85. I have no idea where all these numbers ...

one two three
....blablabla __________ Informacia od ESET NOD32 Antivirus, verzia databazy 4811 (20100127) __________ Tuto spravu preveril ESET NOD32 Antivirus. http://www.eset.sk ...

keeping conditional formatting after paste
I am making a large spreadsheet that others will use and they will have to paste large amounts of data onto the spreadsheet. The issue is that when they paste their data the conditional format that I set up for them is removed. Is there a way to lock the format in place and let them paste still? I would be happy with protecting the sheet and allowing paste to still be an option. Instead of simple 'paste' try using 'paste special' > 'values'. This will then only paste the copied value into the new cell's format. Hope this helps "ericahug...

Formatting Number
I created a custom format for persents, if the percent is negative it has brakets around it, (5%). However, it's only available in the workbook that I created it in. I would like it to be available in every excel file that I open, how do I do that? Thanks Hi, One way would be to create a workbook file (book1.xls) and format it the way you want. Headers and footers and other custom formats. Then save it in the xlstart folder on your c drive. Then every time you open excel this workbook will open. You can either use it or close it and open a new blank. peter >-----Origina...

Help With Multiple Conditional Sum
Hi, everyone... Building formulas is not yet a strong area of mine. I'll keep it concise. The scenario: Given: Columns A, B and D, respectively: Vendor, Date, Amount. I want to build a table of annual sums as follows: Columns: G, H-Q, respectively: Vendor, 1998-2006 Each vendor, then, will be listed once in the Vendor column (G). I should be able to scan across columns H-Q for each vendor row and see how much I spent at any given vendor in any given year. Any working solutions would be greatly appreciated. Thanks, KZ In H1:Q1, input the year numbers In G2:Gn, add all vendor ids ...

euro currency format
recently someone posted asking how to get the ms download for euro formatting. you can download it here. go to office97. excel. add ins. http://office.microsoft.com/home/default.aspx ...

How to merge two Excel files. #3
i m using office XP, i have lots of data in different excel files, each files include two or three worksheet, and all worshett are in different layout, how to merge all this excel files in one excel files, is there any kind of facility in excel ....?.......can any body help for that........? Thanx in advance, BrijLo81. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.522 / Virus Database: 320 - Release Date: 9/29/2003 right click menu on sheet name, "select all sheets" and "Move or Copy" so you ca...

Count the occurence of multiple (4 conditions).
Hi, I am unable to get the correct count using the formula : =SUM(IF(A1:A999=" A",IF(D1:D999="D>d",IF(F1:F999="1",IF(E1:E999="1",1,0))))) I am trying to get no of records with " A" values in Column A, "D>d" values in Column D, "1" values in Column F, "1" values in Column E and Sum all the counts- after applying the nested filters. Can someone help me in debugging the same? Will appreciate early response! Thanks in advance, Try this: =SUMPRODUCT((A1:A999="A")*(D1:D999="...

Cell Formatting #6
Hello, I have formatted my cells as "Text" so that when I type in something like "12-4", Excel doesn't convert the number to a date. But when I copy and paste "12-4" from a web site and paste it, Excel does put it in date form even though I have formatted the cell as "Text". How can I get Excel to accept the copied and pasted info without switching it to a date? Thanks! ...

Losing conditional formatting when printing
Hi, I have a spreadsheet containing amongst others, 10 lists with conditional formatting on every cell (e.g. border,pattern etc). I use VBA code to print these lists but have noticed that when I print more than 1 sheet, only the first few lines print with the con format displayed onscreen. I also noticed that the number of lines printed correctly correspond to the number of lines in the first sheet i.e. if the first sheet contains 10 lines and the second contains 20, only 10 lines on the 2nd sheet will print correctly. If I have more lines on the 1st sheet than the 2nd sheet, all li...

Excel Formating
I input the value of 1A , 2P.. in a cell formatted as Text (This Cell can have any text eg "Suite 100"). When I read this value from VBA I see a value 1.00.00 AM. Can I not retain the Value as 1A, 2P.. I tried to change the format to "General" still no luck.. If you select that cell and look at the formula bar, what do you see? The only way I could get this: Option Explicit Sub test01() MsgBox Range("a1").Value End Sub To return something close to an hour was to change my regional settings (I changed AM to just A and PM to just P) on the Time Tab. Then t...

Protect only the cell format.
User can drag-fill text from one cell to other adjacent cells, but the format in the drag-filled cells is locked. How? ...

change date format
I copy some data from an HTML page and save it on an Excel worksheet. I need to change the date format from dd/mm/yy to mm/dd/yy but it doesn't effect the format change . Any idea why is this happening ? It's probably text and it might have a trailing html character as well. One way to go, use data>text to columns, click next twice, select date and DMY and click finish If that doesn't work you probably have a trailing html character, try to delete it by selecting the range, do edit>replace and in the replace what box hold down alt and type 0160 on the numpad, leave the re...

Using expression builder to format a report field
Is there a way to use expression builder on a report to format a field to currency? I have a field on a report that can be alphanumeric or numeric. If it is numeric I would like to format the data to currency (commas and $). Currently this report will not let me do this in the normal means because as I mentioned, it can be alphanumeric so I cannot jsut chnage the property to currency. Can this be done? "AJ" <AJ@discussions.microsoft.com> wrote in message news:499CD7D2-59A4-4CE5-8C06-68F34D563908@microsoft.com... > Is there a way to use expression builder on a report...

copy conditional formats #2
I've spent the past 2 days researching this issue, but have come up dry. I'm hoping you can help me. I have a range of cell, 2 abreast in this instance, and I have placed conditional formatting on them. 1) if B2 = x than range (B2:D2) is green 2) if B2 = y than range (B2:D2) is yellow 3) if B2 = z than range (B2:D2) is red ... 1) if B6 = x than range (G6:I6) is green 2) if B6 = y than range (G6:I6) is yellow 3) if B6 = z than range (G6:I6) is red This works great. My quandary is "copying" this conditional formatting to other cells. ( Column B->D Rows 2->26 ) and...

hour format
i pretty useless at excel, but im wondering is it possible to format a colum so that excel recongises that colum as hours. ive to setup a spread sheet for the number of hours worked and i want excel to know if i input say 37.5 hours, it will know that its hours and not days or minutes. is this possible sorry for sounding dumb Not by formatting, but you could have a macro that converts your input to time. Chip Pearson has some code at: http://www.cpearson.com/excel/DateTimeEntry.htm And you may want to read more about how excel deals with time (and dates): http://www.cpearson.com/excel/...