Inserting/Displaying Arrow (Up or Down) in a cell on Comparision

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Inserting/Displaying Arrow (Up or Down) in a cell on Comparision

  1. #1
    New Member
    Join Date
    Feb 2013
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Inserting/Displaying Arrow (Up or Down) in a cell on Comparision

     
    Hi,
    I have the as shown below, currently am manually comparing the yellow & green cells (C&D Columns)and accordingly inserting arrow symbols in MoM cells (F&G Columns).. I have to the same thing no.of sheets every month..

    Pls help me with any formula or a macro which will reduce my donkey work..




    Thanks,
    Raj.

  2. #2
    New Member
    Join Date
    Feb 2013
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting/Displaying Arrow (Up or Down) in a cell on Comparision

    Guyss... Can anybody help me out...???

  3. #3
    Board Regular
    Join Date
    Sep 2008
    Posts
    505
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting/Displaying Arrow (Up or Down) in a cell on Comparision


  4. #4
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,051
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting/Displaying Arrow (Up or Down) in a cell on Comparision

    Use a copy of your workbook to try.
    I don't know if it will work on merged cells.

    Something like this:
    (Thanks to snb)
    Code:
    With Range("F2:G2")
    .Font.Size = 22    '????
    .Font.Name = "Wingdings 3"
    .Formula = "=If(RC[-1]
    
    The Char(198, 199 and 200) are up, down and sideways arrows in Wingdings 3 Font

    You can include Select Case to color the arrows.

    For instance
    Code:
    For Each cel In Range("C8:G8")    '<---- Change to fit your requirements
    Select Case cel.Value
    Case 0
    cel.Offset(, 1).Font.Color = vbBlue
    Case Is < 0
    cel.Offset(, 1).Font.Color = vbRed
    Case Is > 0
    cel.Offset(, 1).Font.Color = vbGreen
     End Select
     Next cel
    Note: This is not tailored to your requirements, it is from one of my workbooks.
    I don't have the time now but in the meantime you'll have something to play with.
    If you can't get it working, let us know.
    Good luck.
    There are people who work a lot and make many mistakes. There are people who work a little and make few mistakes. I know people who don't make any mistakes.

  5. #5
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,051
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting/Displaying Arrow (Up or Down) in a cell on Comparision

    This assumes that you have a full year from Jan to Dec in cells B1 to M1.
    The values that are compared are in B4:M4 and B2:M2
    The MoM for each month are from N1 toY1.
    The arrows for each month are from N2/N4 (Merge and Center, Middle Align, Center) toY2/Y4 (Merge and Center, Middle Align, Center).

    On a copy of your workbook, try this.

    Code:
    Sub Try_This()
        Dim c As Range
        With Range("N2:Y2")
            .Font.Size = 36
            .Font.Name = "Wingdings 3"
            .Formula = "=If(R[2]C[-12] c.Offset(-2).Value Then c.Offset(-2, 12).Font.Color = vbGreen
            If c.Value = c.Offset(-2).Value Then c.Offset(-2, 12).Font.Color = vbBlue
        Next c
    End Sub
    I hope someone will change this to a more robust code
    There are people who work a lot and make many mistakes. There are people who work a little and make few mistakes. I know people who don't make any mistakes.

  6. #6
    Board Regular
    Join Date
    Sep 2004
    Posts
    1,051
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Inserting/Displaying Arrow (Up or Down) in a cell on Comparision

      
    Sub Try_This()
    Dim c As Range
    With Range("N2:Y2")
    .Font.Size = 36 '????
    .Font.Name = "Wingdings 3"
    .Formula = "=If(R[2]C[-12] .Value = .Value
    End With
    For Each c In Range("B4:M4")
    If c.Value < c.Offset(-2).Value Then c.Offset(-2, 12).Font.Color = vbRed
    If c.Value > c.Offset(-2).Value Then c.Offset(-2, 12).Font.Color = vbGreen
    If c.Value = c.Offset(-2).Value Then c.Offset(-2, 12).Font.Color = vbBlue
    Next c
    End Sub

    The previous post did not keep the whole code. Tried changing but no luck.
    This post did the same with the code in square brackets and forward slash+code in square brackets so I left these out.
    Could someone tell me what I am doing wrong please
    Last edited by jolivanes; Apr 17th, 2013 at 01:56 AM.
    There are people who work a lot and make many mistakes. There are people who work a little and make few mistakes. I know people who don't make any mistakes.

User Tag List

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