Hi Rachael
You can only do that with VBA.
I think the following code will achieve what you want.
I have assumed that data is in column A. Change all references from A to
your column letter if different.
Sub addComments()
Dim lr As Long, i As Long, diff As Long, colour As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
Range("A1:A" & lr).ClearComments
For i = 2 To lr
diff = Range("A" & i).Value - Range("A" & i - 1).Value
If diff < 0 Then
colour = 45 ' Rose
ElseIf diff = 0 Then
colour = 43 ' Pale Yellow
Else
colour = 42 ' Light Green
End If
Range("A" & i).AddComment
Range("A" & i).Comment.Visible = True
Range("A" & i).Comment.Text Text:=" " & diff & ""
Range("A" & i).Comment.Shape.Select True
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = colour
Selection.ShapeRange.Height = 15.75
Selection.ShapeRange.Width = 28.5
Range("A" & i).Comment.Visible = False
Next i
Application.ScreenUpdating = True
End Sub
Copy the Code above
Alt+F11 to invoke the
VB Editor
InsertModule
Paste code into white pane that appears
Alt+F11 to return to Excel
To use
Alt+F8 to bring up Macros
Highlight the macro name
Run
--
Regards
Roger Govier
wrote in message
...
Actually, I meant to display the difference in a comment, but I'd
forgotten what the correct term was!
So, when I hover over the cell, it will display the difference in the
value depending on the previous cell...
Thanks again,
Rachael
On 22 Sep, 12:00, "Ashish Mathur" wrote:
Hi,
Thank you for your feedback. If I correctly understand your problem, you
would like the difference to be displayed in the cell. If that is indeed
the case, then just go to cell A2 and in the formula bar, type =A2-A1 and
copy downwards.
--
Regards,
Ashsih Mathur
Microsoft Excel MVPwww.ashishmathur.com
wrote in message
...
Thanks Ashsih,
With a bit of tweaking, this has done exactly what I wanted...
Now for the potential complicated bit...
Any way of getting the difference between the cells displayed in a
label?
Not sure if I need to re-post this now as a new question, but thought
i'd ask you first!
Thanks again,
A v.quick and excellent response!
Rachael
On 22 Sep, 11:24, "Ashish Mathur" wrote:
Dear Rachael,
While on cell A2, in Format Conditional formatting, select cell value
is
in
the drop down on the left and then select Less than. In the box to
the
right select cell A1 and remove the $ sign before the row number so
that
it
looks liket $A1. Select the font color desired. Click on Add and add
the
other 2 conditions as well.
--
Regards,
Ashsih Mathur
Microsoft Excel MVPwww.ashishmathur.com
wrote in message
...
To those who understand these things better than me...
I have a column of data that contains information about visitors to
my
website.
E.g.
Column A
1 | 101
2 | 126
3 | 211
4 | 199
5 | 165
6 | 205
I want to apply some conditional formatting to THAT column,
depending
on whether the previous number was lower or higher than the number
in
question.
So,
If A2<A1, then show cell A2 as red (because the number of hits went
down for period 2.
however, if A2A1, then show cell A2 as green (because the number of
hits went up).
and if A2 = A1 then show cell A2 as yellow (no change)
So, the colours for the cells should be :
1 | 101 not coloured, as is the first set of numbers
2 | 126 green (number went up)
3 | 211 green (number went up)
4 | 199 red (number went down)
5 | 165 red (number went down)
6 | 205 green (number went up)
I know that I could simply add an additional column of data, and do
A2-
A1 to see if the value is negative or not, and then colour the cells
according whether a - exists, but i'm trying to produce a
spreadsheet
with as few columns of data as possible.
Any help on this matter would be greatly appreciated. It's really
annoying now, because i'm sure that there's a SIMPLE way of doing
it,
that i've just missed.
Thanks in advance,
Rachael- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -