Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Cincinnati, Ohio, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    colored numbers if over 200 (Excel 2000)

    Howdy,
    I forgot how to write an if statement for a column of numbers. If the number is >200, font should be red instead of black. Can someone please help me out? Thanks

  2. #2
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: colored numbers if over 200 (Excel 2000)

    Good morning

    If you use conditional formating you can change the number to a different colour, if for example whilst in A1 you select 'Format' - 'Conditional Format' and then choose 'Formula is' and enter =A1>200 and then select 'Format' you can change the font colour to red. In A1 type 200 and it stays black, type 201 and it turns red, you can then copy A1's conditional formating by dragging it down as far as you want.

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: colored numbers if over 200 (Excel 2000)

    You can also use a custom format:
    - Select the cells that you want to format.
    - Select Format | Cells...
    - Activate the Number tab.
    - Select the Custom category at the bottom of the list.
    - Enter the following format in the Type box:
    <code>
    <!t>[Black][<=200]General;<!t>[Red][>200]General
    </code>
    - If necessary, replace General with the number format that you want, e.g.
    <code>
    <!t>[Black][<=200]$ #,##0.00;<!t>[Red][>200]$ #,##0.00;General
    </code>
    - Click OK.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Cincinnati, Ohio, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: colored numbers if over 200 (Excel 2000)

    Thank you so much! I now have another quandry. I'm trying to create a pop up box that warns me of duplicate numbers in the same column. I had an if statement written many years ago and can't seem to resurrect it now. Any ideas? Thanks again!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: colored numbers if over 200 (Excel 2000)

    You can use a formula and/or conditional formatting. For example, if you want to check for duplicates in the range A1:A20, the array formula
    <code>
    =MAX(COUNTIF(A1:A20,A1:A20))>1
    </code>
    confirmed with Ctrl+Shift+Enter will return TRUE if there are duplicates, FALSE if all entries are unique (blank cells are ignored).

    You can select the range and apply conditional formatting with the formula
    <code>
    =COUNTIF($A$1:$A$20,A1)>1
    </code>
    See attached sample workbook.
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Cincinnati, Ohio, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: colored numbers if over 200 (Excel 2000)

    Actually, I entered your formula into validation under data and wrote a note indicating duplicate number, warning, do not use. It's working splendidly. Thanks again. Where do you learn all this stuff??? Is it tucked away in your gray matter somewhere? Thanks again.
    Jack

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: colored numbers if over 200 (Excel 2000)

    > Where do you learn all this stuff?

    For a considerable part here, in the Lounge, by studying the replies by others.

Posting Permissions

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