Results 1 to 4 of 4

Conditional Formatting

This is a discussion on Conditional Formatting within the Excel Questions forums, part of the Question Forums category; Hi - This is tricky and hopefully you can help as have yet to find out how to do this. ...

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    2

    Default Conditional Formatting

    Hi - This is tricky and hopefully you can help as have yet to find out how to do this.

    I have a spreadsheet with 185 rows and 100 columns. Each cell has a formula which will produce either a 1,2,3,4,5,6,7 or 8. The color of the cells are based on the number with red being the lowest (1) and green being the highest (8) - using conditional formatting. What I would like to do is add arrows to the cell based on the numbers, in addition to the colors - also using conditional formatting (or any other suggesstion). The odd numbers deserve a DOWN arrow and the even numbers deserve an UP arrow.

    So to boil it down, 1 is Red and it scales to green at #8. The arrows should be up for even numbers and down for odd numbers.

    Please Help!!

    Thanks,

  2. #2
    Board Regular
    Join Date
    Sep 2007
    Location
    Australia
    Posts
    214

    Default Re: Conditional Formatting

    Hi there,

    You can do it all with conditional formatting but you will need to set 8 rules, a rule for each number.

    It depends on what type of arrows you require.

    ALT 30 and ALT 31 are up - down arrows, if you want different arrows find the alt code
    for those arrows.

    I will show 1 rule for the first number 1 then continue in a like manner for the rest of
    your numbers. Use ALT 30 for the Up arrow for even numbers

    First select all the range of the numbers you wish to format > select conditional
    formatting > select "Use a formula..." > type in =A1=1 (if that is the
    first cell in your selected range) > click format > click fill > click red > click number >
    click custom > in the type box highlight the general then while holding down the ALT key type 31 and 3 spaces
    followed by the Hash symbol (you should now see an downwards triangle and a number in the sample box)
    Click OK > Click apply.

    Cheers.
    Last edited by asking; Oct 24th, 2011 at 09:59 PM.
    “You cannot solve a problem with the same mind that created it.” ~ Albert Einstein

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    2

    Default Re: Conditional Formatting

    Hi -

    Thank you for your response. If I may ask a follow up question.

    Unless I am mistaken this formula will change each cell based on the value in cell A1 (so if A1 is '1' then all cells with be red and have a down arrow). Each cell in my worksheet will produce a number between 1-8 and based on each cell I want excel to produce a color and arrow. So all cells with 1 will be red with down arrow; All cells with 2 will be red with up arrow; all cells with 3 will be lighter red with down arrow; etc...

    Thank you for taking the time to help. I greatly appreciate it!

    (I am using excel 2010)

    Best,
    J

  4. #4
    Board Regular
    Join Date
    Sep 2007
    Location
    Australia
    Posts
    214

    Default Re: Conditional Formatting

    Hi again,

    No, A1=1 is a relative reference, it will only colour cells red in the selected range with a 1 in them ~ try it.
    When it looks into the next active cell in the range selected the A1 will change to
    =A2=1 and so on. If the formula was =A$1=1 then it would colour all cells red in the
    columns starting with a 1 If the formula is =$A$1 then if the first cell was a 1 then
    it would colour all cells red, if it was not 1 in cell A1, no colour would be applied.

    When you do your next rule for number 2, for your next colour/format the new formula =A1=2 still needs the same selected range as for the first rule.

    Another thing I thought of was that you may want your arrows on the right of the number, if this is the case ~ change

    in the type box, highlight the general then while holding down the ALT key type 31 and 3 spaces followed by the Hash symbol
    (you should now see an downwards triangle and a number in the sample box) Click OK > Click apply.

    To ~
    in the type box, highlight the general then type the hash key (#) followed by 3 spaces, then while holding down the ALT key type 31
    (you should now see a number then 3 spaces and a downward triangle in the sample box)
    Click OK > Click apply.

    Cheers
    “You cannot solve a problem with the same mind that created it.” ~ Albert Einstein

Like this thread? Share it with others

Like this thread? Share it with 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
  •  


DMCA.com