Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional number format - Possible? (Excel 2000)

    I don't see any way to do this, but I thought I would ask to make sure no one else has found a way. I have a spreadsheet with a column of numbers (imagine that!), the first of which is formatted with a "Currency" style number format, so that it has a dollar sign at the left side of the cell. The column consists of a series of formulas that pull in numbers from a "data entry" area of the sheet, and not all of the cells in the column will always have numbers, depending on the data that is entered in that other area. When the sheet is printed, any rows that don't have data in this column are not included (this is done by means of a macro, that copies rows with data to yet another area, and then prints the parts that got copied). This means, if the first row with the Currency formatted cell has no data, the dollar sign at the head of the column is lost.

    What I need to do is find a way for the first cell in the column that contains data to have the "Currency" formatting, so that it always gets copied to the print range. But Excel's conditional formatting doesn't appear to include any aspect of number formatting, or formatting by style. I had thought of trying to use a formula to add the dollar sign, but then it wouldn't be left-aligned in the cell, would it? I am sure that I can find the proper cell and apply the "Currency" style via code, but before I start fiddling with the existing print macro, I wanted to check if there was another way to do this.

    Thanks!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional number format - Possible? (Excel 2000)

    I would think the easiest thing would be to format (as desired) the top numeric cell (via the macro) after the range is copied.

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional number format - Possible? (Excel 2000)

    As you have figured out, Conditional Formatting can't be used to accomplish this. Steve's solution of having the macro that copies the data for printing format the first cell as currency is the easiest solution to getting the correct print format. However, if you also want the format on the first cell with data on the sheet that you described, that can be done with a little VBA code in one of the worksheet change event routines (which one depends on the exact layout of the sheet). If you want to do that, then would you upload a sample workbook with any propriatery data changed that we could use to develop the code?
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Aug 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional number format - Possible? (Excel 2000)

    I think the formatting of the final report is the most important thing, so I will look into changing the format via macro when the thing is printed (I think I've done something similar elsewhere -- the trick will be finding it!) But I will keep your tip about the worksheet change event in mind, Legare, in case we ever do need to have the dollar sign move around on the original sheet, or in another workbook somewhere. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •