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

dvrajaneesh

New Member
Joined
Feb 14, 2013
Messages
19
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..


Capture.jpg


Thanks,
Raj.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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]<R[-1]C[-1],CHAR(200),IF(RC[-1]=R[-1]C[-1],CHAR(198),CHAR(199)))"
Note: The above formula pertains to one of my sheets and not your example. Change as required.
.Value = .Value
End With

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.
 
Upvote 0
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]<RC[-12],CHAR(200),IF(R[2]C[-12]=RC[-12],CHAR(198),CHAR(199)))"
        .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

I hope someone will change this to a more robust code
 
Upvote 0
Sub Try_This()
Dim c As Range
With Range("N2:Y2")
.Font.Size = 36 '????
.Font.Name = "Wingdings 3"
.Formula = "=If(R[2]C[-12]<RC[-12],CHAR(200),IF(R[2]C[-12]=RC[-12],CHAR(198),CHAR(199)))"
.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:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top