Coloring the Desired cells

Hello,

I have a work sheet in which i have to look for word "Test" and color
the rows below it.
There are different words like "Test 1"  "Test 2" and each set needs a
different color.
Can I get some help with the macro for it?

eg:
Test1
  row 1
  row 2

Test 2
  row 1
  row 2

the number of rows in each group is not constanr.


Thank you,
Harsh

0
10/18/2006 6:49:12 PM
excel 39879 articles. 2 followers. Follow

7 Replies
358 Views

Similar Articles

[PageSpeed] 7

Excel will need to know the logic of the rows and colors to be able to 
determine how many rows to color.  You say the number of rows is not 
constant, but obviously you know how many rows to color.  How do you know 
that?  HTH  Otto
"Harsh" <cooldreamz83@yahoo.com> wrote in message 
news:1161197352.283590.52700@f16g2000cwb.googlegroups.com...
>
> Hello,
>
> I have a work sheet in which i have to look for word "Test" and color
> the rows below it.
> There are different words like "Test 1"  "Test 2" and each set needs a
> different color.
> Can I get some help with the macro for it?
>
> eg:
> Test1
>  row 1
>  row 2
>
> Test 2
>  row 1
>  row 2
>
> the number of rows in each group is not constanr.
>
>
> Thank you,
> Harsh
> 


0
ottokmnop (389)
10/18/2006 10:12:53 PM
The spread sheet has many parameters. The Test 1 condition repeats and
the number of rows is uncertain . So the logic i was thinking search
Test 1 and color until Test 2 and so on...


Otto Moehrbach wrote:
> Excel will need to know the logic of the rows and colors to be able to
> determine how many rows to color.  You say the number of rows is not
> constant, but obviously you know how many rows to color.  How do you know
> that?  HTH  Otto
> "Harsh" <cooldreamz83@yahoo.com> wrote in message
> news:1161197352.283590.52700@f16g2000cwb.googlegroups.com...
> >
> > Hello,
> >
> > I have a work sheet in which i have to look for word "Test" and color
> > the rows below it.
> > There are different words like "Test 1"  "Test 2" and each set needs a
> > different color.
> > Can I get some help with the macro for it?
> >
> > eg:
> > Test1
> >  row 1
> >  row 2
> >
> > Test 2
> >  row 1
> >  row 2
> >
> > the number of rows in each group is not constanr.
> >
> >
> > Thank you,
> > Harsh
> >

0
10/18/2006 10:48:55 PM
That sounds good.  The code could search for any entry that starts with 
"Test" and go from there.  Do you have a list of what color goes with what 
"Test X"?
Is all your data in one column?  What column?  If more than one, what 
columns to search?  Answer these questions and I'll work you up something in 
the morning (USA Easter time).   HTH   Otto
"Harsh" <cooldreamz83@yahoo.com> wrote in message 
news:1161211735.780891.81980@i42g2000cwa.googlegroups.com...
> The spread sheet has many parameters. The Test 1 condition repeats and
> the number of rows is uncertain . So the logic i was thinking search
> Test 1 and color until Test 2 and so on...
>
>
> Otto Moehrbach wrote:
>> Excel will need to know the logic of the rows and colors to be able to
>> determine how many rows to color.  You say the number of rows is not
>> constant, but obviously you know how many rows to color.  How do you know
>> that?  HTH  Otto
>> "Harsh" <cooldreamz83@yahoo.com> wrote in message
>> news:1161197352.283590.52700@f16g2000cwb.googlegroups.com...
>> >
>> > Hello,
>> >
>> > I have a work sheet in which i have to look for word "Test" and color
>> > the rows below it.
>> > There are different words like "Test 1"  "Test 2" and each set needs a
>> > different color.
>> > Can I get some help with the macro for it?
>> >
>> > eg:
>> > Test1
>> >  row 1
>> >  row 2
>> >
>> > Test 2
>> >  row 1
>> >  row 2
>> >
>> > the number of rows in each group is not constanr.
>> >
>> >
>> > Thank you,
>> > Harsh
>> >
> 


0
ottokmnop (389)
10/18/2006 11:52:38 PM
I need to search Test in 2nd column. But the data below test is in
multiple columns.

eg.

Test1
Data1    data2     Data3    data4
Data1    data2     Data3    data4

Test2
Data1    data2     Data3    data4
Data1    data2     Data3    data4

and so on..

The number of columns too are unknown.

Thanks for your help,
Harsh....



Otto Moehrbach wrote:
> That sounds good.  The code could search for any entry that starts with
> "Test" and go from there.  Do you have a list of what color goes with what
> "Test X"?
> Is all your data in one column?  What column?  If more than one, what
> columns to search?  Answer these questions and I'll work you up something in
> the morning (USA Easter time).   HTH   Otto
> "Harsh" <cooldreamz83@yahoo.com> wrote in message
> news:1161211735.780891.81980@i42g2000cwa.googlegroups.com...
> > The spread sheet has many parameters. The Test 1 condition repeats and
> > the number of rows is uncertain . So the logic i was thinking search
> > Test 1 and color until Test 2 and so on...
> >
> >
> > Otto Moehrbach wrote:
> >> Excel will need to know the logic of the rows and colors to be able to
> >> determine how many rows to color.  You say the number of rows is not
> >> constant, but obviously you know how many rows to color.  How do you know
> >> that?  HTH  Otto
> >> "Harsh" <cooldreamz83@yahoo.com> wrote in message
> >> news:1161197352.283590.52700@f16g2000cwb.googlegroups.com...
> >> >
> >> > Hello,
> >> >
> >> > I have a work sheet in which i have to look for word "Test" and color
> >> > the rows below it.
> >> > There are different words like "Test 1"  "Test 2" and each set needs a
> >> > different color.
> >> > Can I get some help with the macro for it?
> >> >
> >> > eg:
> >> > Test1
> >> >  row 1
> >> >  row 2
> >> >
> >> > Test 2
> >> >  row 1
> >> >  row 2
> >> >
> >> > the number of rows in each group is not constanr.
> >> >
> >> >
> >> > Thank you,
> >> > Harsh
> >> >
> >

0
10/19/2006 12:58:12 AM
Is the number of columns constant for all the rows below any given Test X or 
does that vary also?  Otto
"Harsh" <cooldreamz83@yahoo.com> wrote in message 
news:1161219492.893851.94920@m73g2000cwd.googlegroups.com...
>I need to search Test in 2nd column. But the data below test is in
> multiple columns.
>
> eg.
>
> Test1
> Data1    data2     Data3    data4
> Data1    data2     Data3    data4
>
> Test2
> Data1    data2     Data3    data4
> Data1    data2     Data3    data4
>
> and so on..
>
> The number of columns too are unknown.
>
> Thanks for your help,
> Harsh....
>
>
>
> Otto Moehrbach wrote:
>> That sounds good.  The code could search for any entry that starts with
>> "Test" and go from there.  Do you have a list of what color goes with 
>> what
>> "Test X"?
>> Is all your data in one column?  What column?  If more than one, what
>> columns to search?  Answer these questions and I'll work you up something 
>> in
>> the morning (USA Easter time).   HTH   Otto
>> "Harsh" <cooldreamz83@yahoo.com> wrote in message
>> news:1161211735.780891.81980@i42g2000cwa.googlegroups.com...
>> > The spread sheet has many parameters. The Test 1 condition repeats and
>> > the number of rows is uncertain . So the logic i was thinking search
>> > Test 1 and color until Test 2 and so on...
>> >
>> >
>> > Otto Moehrbach wrote:
>> >> Excel will need to know the logic of the rows and colors to be able to
>> >> determine how many rows to color.  You say the number of rows is not
>> >> constant, but obviously you know how many rows to color.  How do you 
>> >> know
>> >> that?  HTH  Otto
>> >> "Harsh" <cooldreamz83@yahoo.com> wrote in message
>> >> news:1161197352.283590.52700@f16g2000cwb.googlegroups.com...
>> >> >
>> >> > Hello,
>> >> >
>> >> > I have a work sheet in which i have to look for word "Test" and 
>> >> > color
>> >> > the rows below it.
>> >> > There are different words like "Test 1"  "Test 2" and each set needs 
>> >> > a
>> >> > different color.
>> >> > Can I get some help with the macro for it?
>> >> >
>> >> > eg:
>> >> > Test1
>> >> >  row 1
>> >> >  row 2
>> >> >
>> >> > Test 2
>> >> >  row 1
>> >> >  row 2
>> >> >
>> >> > the number of rows in each group is not constanr.
>> >> >
>> >> >
>> >> > Thank you,
>> >> > Harsh
>> >> >
>> >
> 


0
ottokmnop (389)
10/19/2006 1:30:15 AM
Yes it varies too. and for each Test X the color should be different.

Test 1 red,
test 2 blue
test 3 green

and thee 3 test conditions repeat in the sheet. Both rows and columns
are not constant.
Thanks.


Otto Moehrbach wrote:
> Is the number of columns constant for all the rows below any given Test X or
> does that vary also?  Otto
> "Harsh" <cooldreamz83@yahoo.com> wrote in message
> news:1161219492.893851.94920@m73g2000cwd.googlegroups.com...
> >I need to search Test in 2nd column. But the data below test is in
> > multiple columns.
> >
> > eg.
> >
> > Test1
> > Data1    data2     Data3    data4
> > Data1    data2     Data3    data4
> >
> > Test2
> > Data1    data2     Data3    data4
> > Data1    data2     Data3    data4
> >
> > and so on..
> >
> > The number of columns too are unknown.
> >
> > Thanks for your help,
> > Harsh....
> >
> >
> >
> > Otto Moehrbach wrote:
> >> That sounds good.  The code could search for any entry that starts with
> >> "Test" and go from there.  Do you have a list of what color goes with
> >> what
> >> "Test X"?
> >> Is all your data in one column?  What column?  If more than one, what
> >> columns to search?  Answer these questions and I'll work you up something
> >> in
> >> the morning (USA Easter time).   HTH   Otto
> >> "Harsh" <cooldreamz83@yahoo.com> wrote in message
> >> news:1161211735.780891.81980@i42g2000cwa.googlegroups.com...
> >> > The spread sheet has many parameters. The Test 1 condition repeats and
> >> > the number of rows is uncertain . So the logic i was thinking search
> >> > Test 1 and color until Test 2 and so on...
> >> >
> >> >
> >> > Otto Moehrbach wrote:
> >> >> Excel will need to know the logic of the rows and colors to be able to
> >> >> determine how many rows to color.  You say the number of rows is not
> >> >> constant, but obviously you know how many rows to color.  How do you
> >> >> know
> >> >> that?  HTH  Otto
> >> >> "Harsh" <cooldreamz83@yahoo.com> wrote in message
> >> >> news:1161197352.283590.52700@f16g2000cwb.googlegroups.com...
> >> >> >
> >> >> > Hello,
> >> >> >
> >> >> > I have a work sheet in which i have to look for word "Test" and
> >> >> > color
> >> >> > the rows below it.
> >> >> > There are different words like "Test 1"  "Test 2" and each set needs
> >> >> > a
> >> >> > different color.
> >> >> > Can I get some help with the macro for it?
> >> >> >
> >> >> > eg:
> >> >> > Test1
> >> >> >  row 1
> >> >> >  row 2
> >> >> >
> >> >> > Test 2
> >> >> >  row 1
> >> >> >  row 2
> >> >> >
> >> >> > the number of rows in each group is not constanr.
> >> >> >
> >> >> >
> >> >> > Thank you,
> >> >> > Harsh
> >> >> >
> >> >
> >

0
10/19/2006 3:36:23 AM
Harsh
    Try this out and see if it does what you want.  Paste all the following 
code into a regular module.  Run this code when the sheet that has all the 
Test data is the active sheet.  If you wish, send me an email and I'll send 
you the small file I used to develop this code.  My email address is 
ottokmnop@comcast.net.  Remove the "nop" from this address.  HTH  Otto

Option Explicit
Dim RngColB As Range, First As Range, Last As Range
Dim RngToColor As Range, i As Range, LastRow As Long
Dim c As Long, ColorNum As Long

Sub ColorTests()
    Call FindFirstTest
    Call ColorData
End Sub

Sub FindFirstTest()
    'Find first instance of "Test"
    Set RngColB = Range("B2", Range("B" & Rows.Count).End(xlUp))
    LastRow = RngColB(RngColB.Count).Row
    Set First = RngColB.Find(What:="Test", _
        After:=RngColB(RngColB.Count), _
        LookAt:=xlPart, SearchOrder:=xlByColumns)
End Sub

Sub ColorData()
    Do
        'In case of no data after First
        If Left(First.Offset(1), 4) = "Test" Then
            Set First = First.Offset(1)
            GoTo LoopAgain
        End If
        Call GetLast
        Select Case Right(First, 1)
            Case "1": ColorNum = 3
            Case "2": ColorNum = 5
            Case "3": ColorNum = 4
        End Select
        Set RngToColor = Range(First.Offset(1), Last)
        For Each i In RngToColor
            Range(i, Cells(i.Row, Columns.Count).End(xlToLeft)) _
                .Interior.ColorIndex = ColorNum
        Next i
        Set First = Last.Offset(1)
LoopAgain:
    Loop Until Last.Row >= LastRow
End Sub

Sub GetLast()
    For c = 2 To 1000
        If Left(First.Offset(c), 4) = "Test" Then
            Set Last = First.Offset(c - 1)
            Exit For
        Else
            If IsEmpty(First.Offset(c).Value) Then
                Set Last = First.Offset(c - 1)
                Exit For
            End If
        End If
    Next c
End Sub

"Harsh" <cooldreamz83@yahoo.com> wrote in message 
news:1161228983.430380.316820@i42g2000cwa.googlegroups.com...
> Yes it varies too. and for each Test X the color should be different.
>
> Test 1 red,
> test 2 blue
> test 3 green
>
> and thee 3 test conditions repeat in the sheet. Both rows and columns
> are not constant.
> Thanks.
>
>
> Otto Moehrbach wrote:
>> Is the number of columns constant for all the rows below any given Test X 
>> or
>> does that vary also?  Otto
>> "Harsh" <cooldreamz83@yahoo.com> wrote in message
>> news:1161219492.893851.94920@m73g2000cwd.googlegroups.com...
>> >I need to search Test in 2nd column. But the data below test is in
>> > multiple columns.
>> >
>> > eg.
>> >
>> > Test1
>> > Data1    data2     Data3    data4
>> > Data1    data2     Data3    data4
>> >
>> > Test2
>> > Data1    data2     Data3    data4
>> > Data1    data2     Data3    data4
>> >
>> > and so on..
>> >
>> > The number of columns too are unknown.
>> >
>> > Thanks for your help,
>> > Harsh....
>> >
>> >
>> >
>> > Otto Moehrbach wrote:
>> >> That sounds good.  The code could search for any entry that starts 
>> >> with
>> >> "Test" and go from there.  Do you have a list of what color goes with
>> >> what
>> >> "Test X"?
>> >> Is all your data in one column?  What column?  If more than one, what
>> >> columns to search?  Answer these questions and I'll work you up 
>> >> something
>> >> in
>> >> the morning (USA Easter time).   HTH   Otto
>> >> "Harsh" <cooldreamz83@yahoo.com> wrote in message
>> >> news:1161211735.780891.81980@i42g2000cwa.googlegroups.com...
>> >> > The spread sheet has many parameters. The Test 1 condition repeats 
>> >> > and
>> >> > the number of rows is uncertain . So the logic i was thinking search
>> >> > Test 1 and color until Test 2 and so on...
>> >> >
>> >> >
>> >> > Otto Moehrbach wrote:
>> >> >> Excel will need to know the logic of the rows and colors to be able 
>> >> >> to
>> >> >> determine how many rows to color.  You say the number of rows is 
>> >> >> not
>> >> >> constant, but obviously you know how many rows to color.  How do 
>> >> >> you
>> >> >> know
>> >> >> that?  HTH  Otto
>> >> >> "Harsh" <cooldreamz83@yahoo.com> wrote in message
>> >> >> news:1161197352.283590.52700@f16g2000cwb.googlegroups.com...
>> >> >> >
>> >> >> > Hello,
>> >> >> >
>> >> >> > I have a work sheet in which i have to look for word "Test" and
>> >> >> > color
>> >> >> > the rows below it.
>> >> >> > There are different words like "Test 1"  "Test 2" and each set 
>> >> >> > needs
>> >> >> > a
>> >> >> > different color.
>> >> >> > Can I get some help with the macro for it?
>> >> >> >
>> >> >> > eg:
>> >> >> > Test1
>> >> >> >  row 1
>> >> >> >  row 2
>> >> >> >
>> >> >> > Test 2
>> >> >> >  row 1
>> >> >> >  row 2
>> >> >> >
>> >> >> > the number of rows in each group is not constanr.
>> >> >> >
>> >> >> >
>> >> >> > Thank you,
>> >> >> > Harsh
>> >> >> >
>> >> >
>> >
> 


0
ottokmnop (389)
10/19/2006 12:35:32 PM
Reply:

Similar Artilces:

too many different formatting cells
I can't open an excel document because whem I try to open it says that too many different formatting cells. How to resolve this and open this document? Maybe... XL: Error Message: Too Many Different Cell Formats http://support.microsoft.com/default.aspx?scid=213904 A few people have said that OpenOffice.Org has been able to open the file. Then they clean it up and save it there. Then excel can open that cleaned up version. http://www.openoffice.org, a 60-104 meg download or a CD jo wrote: > > I can't open an excel document because whem I try to open it says that too >...

Use cell value as cell address
Hello everyone. I have a worksheet "Main" of 39,000 rows in which column B contains a number between 1 and 7,500. Column C is an empty column I have added. The second sheet, "Names" in the book contains a single column - A - of 7,500 names. I want to get the value from the second sheet that matches the number column of the first sheet. In other words, if "Main" cell B3 contains 3780, I want to put the value from "Names" cell A3780 into "Main" C3. How do I do this please? Richard --- Message posted from http://www.ExcelForum.com/ Hi tr...

Formulas showing in cell???
I keep getting a formula showing in the cell after I edit i (occasionally). Show formulas is turned off and edit in cell is on. How can I make the formula go awaw and simply show the results whic when edited the results shows correctly? -- Message posted from http://www.ExcelForum.com Hi probably the cell is formated as 'Text' change the cell format to 'General' and re-enter your formula -- Regards Frank Kabel Frankfurt, Germany > I keep getting a formula showing in the cell after I edit it > (occasionally). Show formulas is turned off and edit in cell is on. >...

Pie Chart color
Hi, Excel 2003. I have a 3 series pie-chart, I simply want to change the color of ONE of the 3 slices in the way I want. Thanks for help. Click the slice; pause; click the slice again, it should now have 'handles' around it. Use Format | Data POINT in menu (if it say Data SERIES, try again) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Freightliner" <freightliner2006@hotmail.com> wrote in message news:87a8c3a5-2d6e-43d2-a0ec-7aa1b25c1296@8g2000hse.googlegroups.com... > Hi, > > Excel 2003. I...

How to get total "conditional sum of cells" in a column?
Hi all, I have dollar amounts in one col, and status in another. I want the sum of those dollar amounts where the corresponding status cell is empty (blank). How do I do this? Thanks for any hints, cdj Status in Column A and dollar amounts in Column B: =SUMPRODUCT((A2:A100="")*B2:B100) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings sandymann2@mailinator.com Replace @mailinator.com with @tiscali.co.uk "sherifffruitfly" <sherifffruitfly@gmail.com> wrote in message news:bc08584b-1338-4b3f-8ab3-cc3e1602e581@n1g2000prb.go...

How to only "paste values" of cells that are not "hidden"?
Hello, In my document, many columns are hidden. Say column B is hidden, and I need to copy columns A and C and paste values into another Excel document. How can I do that? When I select columns A and C, copy and paste values, the other document contains columns A,B and C, instead of just columns A and C. Thank you! Don't drag-select, control-click A and then C, for scattered-selection. Danny On Sun, 17 Jul 2005 16:33:01 -0700, Sam <Sam@discussions.microsoft.com> wrote: > Hello, > > In my document, many columns are hidden. Say column B is hidden, &g...

Color Laserjet Printers
Hi, We want to purchase an HP3505 to use with Dynamics GP 9.0. It is not on the compatiblity list. Is anyone out there using this now? Does anyone have any recommendations for a color laserjet that they are using now with Dynamics GP 9.0? TIA, Laura Laura, We don't have anyone using a 3505, but we do have a customer using an HP Color LaserJet 2840 and it works just fine with GP. I haven't yet seen a problem with GP and any HP printer. The compatibility list typically only has a few models that are even being sold today, so I find it not to be very helpful. Please let us...

Tables and banding color in background
I am creating a template where many tables will need to be used. Our standard is to have banding of rows in the table, and when users want to expand the table, I would like them to be able to have the banding automatically occur. This option appears in Word 2007, but does not function well. Has anyone had success with this functionality? Thanks in advance for your help. Color banding can be applied as part of a table style. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "sharon27lily" <sharon27lily@di...

pasting or moving formula cells without updating formulas
I have a flat spreadsheet with a results page at the end. The results page contains a set of formulae which refer to various cell locations within the body of the spreadsheet in order to return statistical results based on the values in said cells. Now I'd like to add more data to my spreadsheet, so i need to make it bigger; however, when I copy and paste, or select and drag the cells containing the formulae, Excel updates the formulae so that they refer to different cells which bear the same spatial relationship to the formulae as the original referees did before the formulae were ...

how do i copy a formula when cell references r not together
in cell reference H5 i have a formula H4*H3, I have copied this formula through to DG5. In cell reference H7 I have a formula H6*H3 which i have also copied through to DG7.I have formulas right down to cell reference H299 (H298*H3) Is there a quick way to repeat the copy bearing in mind the cell references are not together ie H5, H7, H9 and so on. Jon, Copy cells H6:H7, then select H6:H299 and pastespecial formulas. Just make sure your formula is =H6*$H$3 HTH, Bernie MS Excel MVP "jon104" <jon104@discussions.microsoft.com> wrote in message news:DDAB488A-5CDA-47A2-AD...

Scrolling through cells
I'm having trouble scrolling through the cells using the arrows. When I was typing in a cell I used to be able to press one of the arrow keys and it would automatically go to the next cell. Now when I press the arrow key is sticks the next cell in as part of a formula. I'm not sure what's happened to change this. Can anyone help???? thanks :) You are probably in Extend mode. This happens when you press F8. It shows "EXT" in the Status bar (to the right) Press F8 again to deactivate this. -- Kind regards, Niek Otten Microsoft MVP - Excel "alice" &l...

Overwriting a cell with a formula without deleting the formula
Hello. I am creating an Expense Report worksheet and have created a simple formula that will calculate mileage based on total miles. Below is my worksheet data. A B C D 1 Expense Type Acct. Code Total Miles Amount 2 Airfare 11111 $250.00 3 Mileage 22222 20 $10.00 I am trying to figure out a way to create a conditional formula so that IF Expense...

Highlight changes within cell
Good morning! Using Excel 2003 I need to highlight the changes I’m making within a cell. For example: In cell B2, is the customer’s original order quantity of 200. I revise it to show 225 and I’d like the cell to be highlighted in yellow. I can then copy and paste the info into an email to show the customer which items have been revised. I’ve tried using Track Changes, but it seems that I have to click on the Track Changes button each time I open the workbook. It also doesn’t keep the revision highlighted for a copy and paste. I have 20+ worksheets within the workbook an...

How do I use text in a cell as a range name in a formula
If cell A1 had the text TEST in it and TEST is the name I have given to a group of cells using the name box what formula can I use to give me the sum of TEST, thats is the sum of the cells in the group called TEST. I understand that I can simply have =SUM(TEST), but I want the formula to refer to Cell A1 to get the name ie =SUM(A1) doesn't work obviously Any help appreciated Thank you In this case, you want to use the INDIRECT function. E.g., =SUM(INDIRECT(A1)) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kiwi" &...

deleting duplicate cells
I am back again... Thanks to everyone's help here last time, I was able to finish all my work and do it correctly. Ken- I asked last time i was here about deleting duplicate cells. Some of the names(address, etc) are repeated in my sheet. I want to delete the extra cell of the people who are in here twice. Not jus the cell but their record, name, address, city, state zip when they are in their twice so that they will only be listed once. You told me how to do this once but i cant find where it is on the board. Thanks for all the help.... BR4 -----------------------------------------...

Bug? Multiple values in merged cells
I found that merged cells can contain multiple values. Steps to reproduce: Type 1,2,3,4 in a1:d1 type sum(a1:d1) in e1 Select a1:b1 and merge Warning : MultipleData, overwrite? Say yes to merge Select the merged a1:b1 cells Copy Select c1 PasteSpecial Formats No warning.. no overwrite. c1:d1 are now merged BUT d1 still contains a value... and the SUM of a1:d1 = 8 !! Also happens with FormatPainter etc Behaviour observed in xl97,xlXP and xl2003 Error checking will find no fault in the sheet... and you can spend ages to find out WHY your cross sums dont match! (although now that i fou...

Find Blank Cells
I wish to replace the blank cells in a large database with a zero (0). I cannot figure out how to find a blank cell using the Find and Replace option under the Edit menu. Can anyone show me the way? Hi Peter, I always use CTRL+H to bring up the find and replace menu, leave the fine part empty, and put in what you want to replace it with....however, I do notice you say in a "database"? Do you mean in Access?? Dave M. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Exc...

COLOUR CHANGE IS A CELL
Cyber taz What I am trying to do is use say a red coulured cell but when text is added to the cell it changes colour to green. The other thing if possible a drop down menu in a cell so when clicked on it offers three of four selection For example today tomorow yesterday day after And once one wes highlighted it would show in the cell. Thankls for your help it is really apprereciated. You can use data validation to create a dropdown list in a cell. There are instructions in Excel's help, and here: http://www.contextures.com/xlDataVal01.html Mav wrote: > Cyber taz > What I am...

CSV file with 13space characters in blank cells
In my office we've just received a recurring report which has been modified (by someone. Previously (a CSV file) the Data area (say A1:M30 ) had numbers or blank cells representing 0 (zero) values. Now, however the blank cells ARE NOT BLANK (although they appear blank) all cells without numbers have 13 hard-space values in them, which is causing #VALUE! problems; I temporarily added an intermediate sheet with formula: =IF(ISNUMBER(VALUE(MySheet!B2)),MySheet!B2,0) to eliminate the #Value! problem; Is there a better way? I'm sure there is, just not certain at this point in time. Any ...

Hidden Cells #2
I have user who has a spreadsheet and the cells on colum A appear to be empty. However when you click on a cell, the information appears above in the field where you can change text. That field has a lower case fx in front of it. Anyhow I think the user clicked a setting the hides the information in the cell. Any help would be appreciated. Thanks. Hi Anthony Maybe the font is set to white -- Regards Ron de Bruin http://www.rondebruin.nl "Anthony" <Anthony@discussions.microsoft.com> wrote in message news:91D96835-23ED-4985-BE65-45187475EE03@microsoft.com... > I ha...

how do I change list order in color catagory assignment in office
only 15 color catagories can be displayed in color assignment in office outlook 2007. I wish to change the order of the list. I tried to number them 1-15, but it did not change the list. It did show 10-15 in the top of the list, and 1-9 on the bottom. I wish it to show them in order of 1-15, not necessarily with numbers, but with the color and catagory that I have assigne to them. any help would be appreciated THNX ...

How can I write in a text in a cell using numbers and the letters.
I'm trying to get some codes in for example 511E09 is one fo them but when I try to enter another code it makes the code a formula and it changes to 5.11E+09 why is that? I tried changing it by formatting cells to text, but that doesn't change it. Waht can I do? Clear the data, change it to text, and then enter it. -- HTH RP (remove nothere from the email address if mailing direct) "Sandy" <Sandy@discussions.microsoft.com> wrote in message news:9C474C50-E47D-43EB-A4F0-B4D21141E39F@microsoft.com... > I'm trying to get some codes in for example 511E09 i...

How do I format a cell to display measurement units with symbols . #2
How do I format a cell to display measurement units with symbols in Excel 2000? (ie. " for inch, etc.) Thanks, Mandy One way: Format/Cells/Number/Custom 0\" In article <D7589B6A-8E82-437E-B4D5-BAF36BE237E1@microsoft.com>, "Mandy" <Mandy@discussions.microsoft.com> wrote: > How do I format a cell to display measurement units with symbols in Excel > 2000? > (ie. " for inch, etc.) > > Thanks, > > Mandy This is a multi-part message in MIME format. ------=_NextPart_000_0007_01C53500.B38D6F30 Content-Type: text/plain; charse...

how can i color points in a plot with a third data series?
i have 3 columns of data, i would like one as x, one as y, and one as the color of the points. is this possible? You may wish to visit Jon's site http://peltiertech.com/Excel/ChartsHowTo/index.html to get more familiar with Excel cahrting. Then come back and get any extra help needed best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "aaron" <aaron@discussions.microsoft.com> wrote in message news:70D745A9-7F7C-4222-8562-1480EC930077@microsoft.com... > i have 3 columns of data, i would like one as x, one as y,...

How do I count the number of cells
I have several dozens columns and 52 rows containing Data. All the columns are labled Odd or Even In the final (right most) Column I want to count the number of times that that coulmn has data in it The last row belows shows what the answer should be Total Total Odd Even Odd Even Odd Even Odd 5 1 2 ????? ????? 1 7 6 3 ????? ????? 5 1 2 ????? ????? 1 2 9 3 1 3 PLease help =COUNTA() - will count the number of non blank cells. In the brackets place the range name (for example A1:A2). Please hit Yes if my comments have helped. Thanks. &...